PostgreSQL 9.5 引入了一项新功能，即 UPSERT（insert on conflict do）。当插入遇到约束错误时，直接返回或者改为执行 UPDATE。

UPSERT 语法

UPSERT 的语法如下所示。PostgreSQL 9.5 以前的版本，可以通过函数或者 with 语法来实现与 UPSERT 类似的功能。

    1. Command:     INSERT  
    2. Description: create new rows in a table  
    3. Syntax:  
    4. [ WITH [ RECURSIVE ] with_query [, ...] ]  
    5. INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    6.     { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    7.     [ ON CONFLICT [ conflict_target ] conflict_action ]  
    8.     [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
    10. where conflict_target can be one of:  
    12.     ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    13.     ON CONSTRAINT constraint_name  
    15. and conflict_action is one of:  
    17.     DO NOTHING  
    18.     DO UPDATE SET { column_name = { expression | DEFAULT } |  
    19.                     ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
    20.                     ( column_name [, ...] ) = ( sub-SELECT )  
    21.                   } [, ...]  
    22.               [ WHERE condition ]

PostgreSQL 9.5 及以上版本的 UPSERT 用法示例

  • 执行如下命令，创建一张测试表，其中一个字段为唯一键或者主键。
    1. create table test(id int primary key, info text, crt_time timestamp);

  • 执行如下任一命令，选择插入数据时，若数据存在是进行更新还是直接返回。

    • 不存在则插入，存在则更新，其命令如下所示：
    1. test03=# insert into test values (1,'test',now()) on conflict (id) do update set     info=excluded.info,crt_time=excluded.crt_time;  
    2. INSERT 0 1  
    4. test03=# select * from test;  
    5. id | info |          crt_time            
    6. ----+------+----------------------------  
    7. 1 | test | 2017-04-24 15:27:25.393948  
    8. (1 row)  
    10. test03=# insert into test values (1,'hello digoal',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;  
    11. INSERT 0 1  
    13. test03=# select * from test;  
    14. id |     info     |          crt_time            
    15. ----+--------------+----------------------------  
    16. 1 | hello digoal | 2017-04-24 15:27:39.140877  
    17. (1 row)
  • 不存在则插入，存在则直接返回，即不做任何处理，其命令如下所示：
    1. test03=# insert into test values (1,'hello digoal',now()) on conflict (id) do nothing;  
    2. INSERT 0 0  
    3. test03=# insert into test values (1,'pu',now()) on conflict (id) do nothing;  
    4. INSERT 0 0  
    5. test03=# insert into test values (2,'pu',now()) on conflict (id) do nothing;  
    6. INSERT 0 1  
    7. test03=# select * from test;  
    8. id |     info     |          crt_time            
    9. ----+--------------+----------------------------  
    10. 1 | hello digoal | 2017-04-24 15:27:39.140877  
    11. 2 | pu           | 2017-04-24 15:28:20.37392  
    12. (2 rows)

PostgreSQL 9.5 以下版本的 UPSERT 用法示例

您可以根据需求，通过如下三种方法实现类似 UPSERT 的功能。

  • 通过函数实现。
    1.   test03=# create or replace function f_upsert(int,text,timestamp) returns void as $$  
    2.   declare  
    3.     res int;  
    4.   begin  
    5.     update test set info=$2,crt_time=$3 where id=$1;  
    6.     if not found then  
    7.       insert into test (id,info,crt_time) values ($1,$2,$3);  
    8.     end if;  
    9.     exception when others then  
    10.       return;  
    11.   end;  
    12.   $$ language plpgsql strict;  
    13.   CREATE FUNCTION  
    15.   test03=# select f_upsert(1,'digoal',now()::timestamp);  
    16.    f_upsert   
    17.   ----------  
    19.   (1 row)  
    21.   test03=# select * from test;  
    22.    id |  info  |          crt_time            
    23.   ----+--------+----------------------------  
    24.     2 | pu     | 2017-04-24 15:28:20.37392  
    25.     1 | digoal | 2017-04-24 15:31:29.254325  
    26.   (2 rows)  
    28.   test03=# select f_upsert(1,'digoal001',now()::timestamp);  
    29.    f_upsert   
    30.   ----------  
    32.   (1 row)  
    34.   test03=# select * from test;  
    35.    id |   info    |         crt_time            
    36.   ----+-----------+---------------------------  
    37.     2 | pu        | 2017-04-24 15:28:20.37392  
    38.     1 | digoal001 | 2017-04-24 15:31:38.0529  
    39.   (2 rows)  
    41.   test03=# select f_upsert(3,'hello',now()::timestamp);  
    42.    f_upsert   
    43.   ----------  
    45.   (1 row)  
    47.   test03=# select * from test;  
    48.    id |   info    |         crt_time            
    49.   ----+-----------+---------------------------  
    50.     2 | pu        | 2017-04-24 15:28:20.37392  
    51.     1 | digoal001 | 2017-04-24 15:31:38.0529  
    52.     3 | hello     | 2017-04-24 15:31:49.14291  
    53.   (3 rows)

  • 通过 WITH 语法，用法 1，操作步骤如下。

    • 执行如下命令，创建一张测试表，其中一个字段为唯一键或者主键。
    1. create table test(id int primary key, info text, crt_time timestamp);
  • 执行如下命令，若数据存在则更新，不存在则插入。
    1. 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);
  • 执行如下命令，替换变量，进行测试。同时插入一条不存在的值，只有一个会话成功，另一个会话会报主键约束错误。
    1. 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，操作步骤如下。

    • 执行如下命令，创建一张数据表，即使表没有主键或者唯一约束，也能保证并发。
    1. create table test(id int, info text, crt_time timestamp);

  • 进行如下任一操作步骤，选择记录不存在时，对于同一条数据更新的处理结果。

    • 对于记录不存在，可以保证只有一个 session 插入数据，对于同一条数据更新，先来的 session 会 lock着记录，后来的 session 会 wait。操作步骤如下所示：

      • 执行如下命令，确定对数据更新的处理结果。
    1. with     
    2. w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),    
    3. upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *)    
    4. insert into test select $id, $info, $crt_time from w1     
    5. where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);
  1.   -

替换变量，进行测试。

    1. with     
    2. w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),    
    3. upsert as (update test set info='digoal0123',crt_time=now() where id=1 returning *)    
    4. insert into test select 1, 'digoal0123', now() from w1     
    5.  where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);    
    7. INSERT 0 0  
    9. test03=# select * from test;  
    10. id |    info    |         crt_time            
    11. ----+------------+---------------------------  
    12. 2 | pu         | 2017-04-24 15:28:20.37392  
    13. 3 | hello      | 2017-04-24 15:31:49.14291  
    14. 1 | digoal0123 | 2017-04-24 15:31:38.0529  
    15. (3 rows)  
    17. with     
    18. w1 as(select ('x'||substr(md5('4'),1,16))::bit(64)::bigint as tra_id),    
    19. upsert as (update test set info='digoal0123',crt_time=now() where id=4 returning *)    
    20. insert into test select 4, 'digoal0123', now() from w1     
    21.  where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=4);    
    23. INSERT 0 1  
    25. test03=# select * from test;  
    26. id |    info    |          crt_time            
    27. ----+------------+----------------------------  
    28. 2 | pu         | 2017-04-24 15:28:20.37392  
    29. 3 | hello      | 2017-04-24 15:31:49.14291  
    30. 1 | digoal0123 | 2017-04-24 15:31:38.0529  
    31. 4 | digoal0123 | 2017-04-24 15:38:39.801908  
    32. (4 rows)
  1. -

对于记录不存在，可以保证只有一个 session 插入数据，对于同一条数据更新，先来的 session 会更新数据，后来的 session 不等待，直接失败。操作步骤如下所示：

  1.   -

执行如下命令，确定对数据更新的处理结果。

    1. with w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),    
    2. 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 *)    
    3. insert into test select $id,$info,$crt_time from w1   
    4. where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);
  1.   -

替换变量，进行测试。

    1. with w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),    
    2. upsert as (update test set info='test',crt_time=now() from w1 where             pg_try_advisory_xact_lock(tra_id) and id=1 returning *)    
    3. insert into test select 1,'test',now() from w1   
    4.  where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);    
    6. INSERT 0 0  
    8. test03=# select * from test;  
    9. id |    info    |          crt_time            
    10. ----+------------+----------------------------  
    11. 2 | pu         | 2017-04-24 15:28:20.37392  
    12. 3 | hello      | 2017-04-24 15:31:49.14291  
    13. 4 | digoal0123 | 2017-04-24 15:42:50.912887  
    14. 1 | test       | 2017-04-24 15:44:44.245167  
    15. (4 rows)