PostgreSQL INSERT ON CONFLICT不存在则插入，存在则更新

近期有一个需求，向一张数据库表插入数据，如果是新数据则执行插入动作，如果插入的字段和已有字段重复，则更新该行对应的部分字段。

PostgreSQL INSERT ON CONFLICT不存在则插入，存在则更新

1、不存在则插入，存在则更新

insert into test values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;

执行操作：INSERT 0 1
查看结果：select * from test;
id | info | crt_time ----+------+----------------------------
1 | test | 2017-04-24 15:27:25.393948 (1 row)

insert into test values (1,'hello digoal',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;
INSERT 0 1
查看结果：select * from test;
id | info | crt_time ----+--------------+----------------------------
1 | hello digoal | 2017-04-24 15:27:39.140877 (1 row)

EXCLUDED 代指要插入的记录

2、不存在则插入，存在则直接返回(不做任何处理)

insert into test values (1,'hello digoal',now()) on conflict (id) do nothing;
INSERT 0 0 insert into test values (1,'pu',now()) on conflict (id) do nothing;
INSERT 0 0 insert into test values (2,'pu',now()) on conflict (id) do nothing; 
INSERT 0 1 select * from test; id | info | crt_time ----+--------------+----------------------------
 1 | hello digoal | 2017-04-24 15:27:39.140877 2 | pu | 2017-04-24 15:28:20.37392 (2 rows) 
--PostgreSQL 9.5 引入了一项新功能，UPSERT(insert on conflict do)，当插入遇到约束错误时，直接返回，或者改为执行UPDATE。 

PostgreSQL查看版本信息 ：select version();

PostgreSQL 其他常见约束语法添加

1. 添加主键

alter table goods add primary key(sid);

2. 添加外键

alter table orders add foreign key(goods_id) references goods(sid) on update cascade on delete cascade;

on update cascade: 被引用行更新时，引用行自动更新；

on update restrict: 被引用的行禁止更新；

on delete cascade: 被引用行删除时，引用行也一起删除；

on dellete restrict: 被引用的行禁止删除；

3. 删除外键

alter table orders drop constraint orders_goods_id_fkey;

4. 添加唯一约束

alter table goods add constraint unique_goods_sid unique(sid);

5. 删除默认值

alter table goods alter column sid drop default;

6. 修改字段的数据类型

alter table goods alter column sid type character varying;

7. 重命名字段

alter table goods rename column sid to ssid;

 

 

insert into test_t3 values (1,1,'dddd'),(1,2,'eee'),(2,1,'ccc'),(2,2,'dddd') on conflict (t_id,t1_id) do update set t_name=excluded.t_name;