最佳实践-PostgreSQL-PostgreSQL UPSERT 的功能与用法

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 ]

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)

• 
  通过函数实现。
  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. 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)
• 
  对于记录不存在，可以保证只有一个 session 插入数据，对于同一条数据更新，先来的 session 会更新数据，后来的 session 不等待，直接失败。操作步骤如下所示：
  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. 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)