PostgreSQL 9.5引入了一项新功能，即UPSERT（insert on conflict do）。当插入遇到约束错误时，直接返回或者改为执行UPDATE。
UPSERT的语法如下所示。PostgreSQL 9.5以前的版本，可以通过函数或者with语法来实现与UPSERT类似的功能。
- 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 }
- [ ON CONFLICT [ conflict_target ] conflict_action ]
- [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
- where conflict_target can be one of:
- ( { 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 ]
执行如下命令，创建一张测试表，其中一个字段为唯一键或者主键。
- create table test(id int primary key, info text, crt_time timestamp);
执行如下任一命令，选择插入数据时，若数据存在是进行更新还是直接返回。
不存在则插入，存在则更新，其命令如下所示：
- test03=# insert into test values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;
- INSERT 0 1
- test03=# select * from test;
- id | info | crt_time
- ----+------+----------------------------
- 1 | test | 2017-04-24 15:27:25.393948
- (1 row)
- test03=# 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
- test03=# select * from test;
- id | info | crt_time
- ----+--------------+----------------------------
- 1 | hello digoal | 2017-04-24 15:27:39.140877
- (1 row)
不存在则插入，存在则直接返回，即不做任何处理，其命令如下所示：
- test03=# insert into test values (1,'hello digoal',now()) on conflict (id) do nothing;
- INSERT 0 0
- test03=# insert into test values (1,'pu',now()) on conflict (id) do nothing;
- INSERT 0 0
- test03=# insert into test values (2,'pu',now()) on conflict (id) do nothing;
- INSERT 0 1
- test03=# 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)
您可以根据需求，通过如下三种方法实现类似UPSERT的功能：
通过函数实现。
- test03=# create or replace function f_upsert(int,text,timestamp) returns void as $$
- declare
- res int;
- begin
- update test set info=$2,crt_time=$3 where id=$1;
- if not found then
- insert into test (id,info,crt_time) values ($1,$2,$3);
- end if;
- exception when others then
- return;
- end;
- $$ language plpgsql strict;
- CREATE FUNCTION
- test03=# select f_upsert(1,'digoal',now()::timestamp);
- f_upsert
- ----------
- (1 row)
- test03=# select * from test;
- id | info | crt_time
- ----+--------+----------------------------
- 2 | pu | 2017-04-24 15:28:20.37392
- 1 | digoal | 2017-04-24 15:31:29.254325
- (2 rows)
- test03=# select f_upsert(1,'digoal001',now()::timestamp);
- f_upsert
- ----------
- (1 row)
- test03=# select * from test;
- id | info | crt_time
- ----+-----------+---------------------------
- 2 | pu | 2017-04-24 15:28:20.37392
- 1 | digoal001 | 2017-04-24 15:31:38.0529
- (2 rows)
- test03=# select f_upsert(3,'hello',now()::timestamp);
- f_upsert
- ----------
- (1 row)
- test03=# select * from test;
- id | info | crt_time
- ----+-----------+---------------------------
- 2 | pu | 2017-04-24 15:28:20.37392
- 1 | digoal001 | 2017-04-24 15:31:38.0529
- 3 | hello | 2017-04-24 15:31:49.14291
- (3 rows)
通过WITH语法，用法1，操作步骤如下：
执行如下命令，创建一张测试表，其中一个字段为唯一键或者主键。
- create table test(id int primary key, info text, crt_time timestamp);
执行如下命令，若数据存在则更新，不存在则插入。
- with upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *) insert into test select $id,$info,$crt_time where not exists (select 1 from upsert where id=$id);
执行如下命令，替换变量，进行测试。同时插入一条不存在的值，只有一个会话成功，另一个会话会报主键约束错误。
- with upsert as (update test set info='test',crt_time=now() where id=1 returning *) insert into test select 1,'test',now() where not exists (select 1 from upsert where id=1);
通过WITH语法，用法2，操作步骤如下：
执行如下命令，创建一张数据表，即使表没有主键或者唯一约束，也能保证并发。
- create table test(id int, info text, crt_time timestamp);
进行如下任一操作步骤，选择记录不存在时，对于同一条数据更新的处理结果。
对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会lock记录，后来的session会wait。操作步骤如下所示：
执行如下命令，确定对数据更新的处理结果。
- with
- w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),
- upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *)
- insert into test select $id, $info, $crt_time from w1
- where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);
替换变量，进行测试。
- with
- w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),
- upsert as (update test set info='digoal0123',crt_time=now() where id=1 returning *)
- insert into test select 1, 'digoal0123', now() from w1
- where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);
- INSERT 0 0
- test03=# select * from test;
- id | info | crt_time
- ----+------------+---------------------------
- 2 | pu | 2017-04-24 15:28:20.37392
- 3 | hello | 2017-04-24 15:31:49.14291
- 1 | digoal0123 | 2017-04-24 15:31:38.0529
- (3 rows)
- with
- w1 as(select ('x'||substr(md5('4'),1,16))::bit(64)::bigint as tra_id),
- upsert as (update test set info='digoal0123',crt_time=now() where id=4 returning *)
- insert into test select 4, 'digoal0123', now() from w1
- where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=4);
- INSERT 0 1
- test03=# select * from test;
- id | info | crt_time
- ----+------------+----------------------------
- 2 | pu | 2017-04-24 15:28:20.37392
- 3 | hello | 2017-04-24 15:31:49.14291
- 1 | digoal0123 | 2017-04-24 15:31:38.0529
- 4 | digoal0123 | 2017-04-24 15:38:39.801908
- (4 rows)
对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会更新数据，后来的session不等待，直接失败。操作步骤如下所示：
执行如下命令，确定对数据更新的处理结果。
- with w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),
- upsert as (update test set info=$info,crt_time=$crt_time from w1 where pg_try_advisory_xact_lock(tra_id) and id=$id returning *)
- insert into test select $id,$info,$crt_time from w1
- where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);
替换变量，进行测试。
- with w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),
- upsert as (update test set info='test',crt_time=now() from w1 where pg_try_advisory_xact_lock(tra_id) and id=1 returning *)
- insert into test select 1,'test',now() from w1
- where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);
- INSERT 0 0
- test03=# select * from test;
- id | info | crt_time
- ----+------------+----------------------------
- 2 | pu | 2017-04-24 15:28:20.37392
- 3 | hello | 2017-04-24 15:31:49.14291
- 4 | digoal0123 | 2017-04-24 15:42:50.912887
- 1 | test | 2017-04-24 15:44:44.245167
- (4 rows)
