PostgreSQL 9.5 引入了一项新功能,UPSERT(insert on conflict do),当插入遇到约束错误时,直接返回,或者改为执行UPDATE。
语法如下:
Command: INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
where conflict_target can be one of: [ ON CONFLICT [ conflict_target ] conflict_action ]
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
PostgreSQL 9.5以前的版本,可以通过函数,或者with语法来实现UPSERT类似的功能。
用法举例:
创建测试表:
create table test(id int
primary key, info text, crt_time timestamp);
其中id为主键
插入数据:
insert into test
values (
1,'test',now())
on conflict (id)
do update set info=
excluded.info,crt_time=
excluded.crt_time;
第一次插入时,id不冲突,所以正常插入
再次插入数据:
insert into test
values (
1,'hello digoal',now())
on conflict (id)
do update set info=excluded.info,crt_time=excluded.crt_time;
此时,如果是没有写on conflict 语句,正常情况会包错.
但是这里写了之后,并没有报错,数据正常插入了.
但是查看表中数据会发现,只有最后一次插入的数据:
values (1,'hello digoal',now())
之前插入的数据不见了.
所以成功执行了conflict 后的update语句