全部产品
云数据库 RDS 版

PostgreSQL UPSERT 的功能与用法

更新时间:2017-06-07 13:26:11   分享:   

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 ] [, ...] ]
  9. where conflict_target can be one of:
  10. ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
  11. ON CONSTRAINT constraint_name
  12. and conflict_action is one of:
  13. DO NOTHING
  14. DO UPDATE SET { column_name = { expression | DEFAULT } |
  15. ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
  16. ( column_name [, ...] ) = ( sub-SELECT )
  17. } [, ...]
  18. [ WHERE condition ]

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

  1. 执行如下命令,创建一张测试表,其中一个字段为唯一键或者主键。

    1. create table test(id int primary key, info text, crt_time timestamp);
  2. 执行如下任一命令,选择插入数据时,若数据存在是进行更新还是直接返回。

    • 不存在则插入,存在则更新,其命令如下所示:

      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
      3. test03=# select * from test;
      4. id | info | crt_time
      5. ----+------+----------------------------
      6. 1 | test | 2017-04-24 15:27:25.393948
      7. (1 row)
      8. test03=# insert into test values (1,'hello digoal',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;
      9. INSERT 0 1
      10. test03=# select * from test;
      11. id | info | crt_time
      12. ----+--------------+----------------------------
      13. 1 | hello digoal | 2017-04-24 15:27:39.140877
      14. (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
    14. test03=# select f_upsert(1,'digoal',now()::timestamp);
    15. f_upsert
    16. ----------
    17. (1 row)
    18. test03=# select * from test;
    19. id | info | crt_time
    20. ----+--------+----------------------------
    21. 2 | pu | 2017-04-24 15:28:20.37392
    22. 1 | digoal | 2017-04-24 15:31:29.254325
    23. (2 rows)
    24. test03=# select f_upsert(1,'digoal001',now()::timestamp);
    25. f_upsert
    26. ----------
    27. (1 row)
    28. test03=# select * from test;
    29. id | info | crt_time
    30. ----+-----------+---------------------------
    31. 2 | pu | 2017-04-24 15:28:20.37392
    32. 1 | digoal001 | 2017-04-24 15:31:38.0529
    33. (2 rows)
    34. test03=# select f_upsert(3,'hello',now()::timestamp);
    35. f_upsert
    36. ----------
    37. (1 row)
    38. test03=# select * from test;
    39. id | info | crt_time
    40. ----+-----------+---------------------------
    41. 2 | pu | 2017-04-24 15:28:20.37392
    42. 1 | digoal001 | 2017-04-24 15:31:38.0529
    43. 3 | hello | 2017-04-24 15:31:49.14291
    44. (3 rows)
  • 通过 WITH 语法,用法 1,操作步骤如下。

    1. 执行如下命令,创建一张测试表,其中一个字段为唯一键或者主键。

      1. create table test(id int primary key, info text, crt_time timestamp);
    2. 执行如下命令,若数据存在则更新,不存在则插入。

      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);
    3. 执行如下命令,替换变量,进行测试。同时插入一条不存在的值,只有一个会话成功,另一个会话会报主键约束错误。

      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. 执行如下命令,创建一张数据表,即使表没有主键或者唯一约束,也能保证并发。

      1. create table test(id int, info text, crt_time timestamp);
    2. 进行如下任一操作步骤,选择记录不存在时,对于同一条数据更新的处理结果。

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

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

          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);
        2. 替换变量,进行测试。

          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);
          6. INSERT 0 0
          7. test03=# select * from test;
          8. id | info | crt_time
          9. ----+------------+---------------------------
          10. 2 | pu | 2017-04-24 15:28:20.37392
          11. 3 | hello | 2017-04-24 15:31:49.14291
          12. 1 | digoal0123 | 2017-04-24 15:31:38.0529
          13. (3 rows)
          14. with
          15. w1 as(select ('x'||substr(md5('4'),1,16))::bit(64)::bigint as tra_id),
          16. upsert as (update test set info='digoal0123',crt_time=now() where id=4 returning *)
          17. insert into test select 4, 'digoal0123', now() from w1
          18. where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=4);
          19. INSERT 0 1
          20. test03=# select * from test;
          21. id | info | crt_time
          22. ----+------------+----------------------------
          23. 2 | pu | 2017-04-24 15:28:20.37392
          24. 3 | hello | 2017-04-24 15:31:49.14291
          25. 1 | digoal0123 | 2017-04-24 15:31:38.0529
          26. 4 | digoal0123 | 2017-04-24 15:38:39.801908
          27. (4 rows)
      • 对于记录不存在,可以保证只有一个 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);
        2. 替换变量,进行测试。

          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);
          5. INSERT 0 0
          6. test03=# select * from test;
          7. id | info | crt_time
          8. ----+------------+----------------------------
          9. 2 | pu | 2017-04-24 15:28:20.37392
          10. 3 | hello | 2017-04-24 15:31:49.14291
          11. 4 | digoal0123 | 2017-04-24 15:42:50.912887
          12. 1 | test | 2017-04-24 15:44:44.245167
          13. (4 rows)
本文导读目录
本文导读目录
以上内容是否对您有帮助?