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语句
