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;