edit-icon download-icon

Functions and usage of PostgreSQL UPSERT

Last Updated: Feb 07, 2018

UPSERT (insert on conflict do) is a new function of PostgreSQL 9.5. If a constraint error occurs while data is inserted, the error is returned directly or the UPDATE operation is performed.

UPSERT syntax

The UPSERT syntax is as follows. For versions earlier than PostgreSQL 9.5, you can use functions or the WITH syntax to implement functions similar to 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 ]

Example of using UPSERT in PostgreSQL 9.5 and later

  1. Run the following command to create a test table with one field as the unique key or primary key.

    1. create table test(id int primary key, info text, crt_time timestamp);
  2. Run either of the following commands to determine, while data is inserted, whether to update the data or return the data directly if the data exists.

    • Insert the data if it does not exist, or update the data if it exists. The command is as follows.

      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)
    • Insert the data if it does not exist, or return the data directly without processing if it exists. The command is as follows.

      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)

Example of using UPSERT in PostgreSQL earlier than 9.5

You can use one of the following methods to implement functions similar to UPSERT:

  • Use functions.

    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)
  • Use the WITH syntax in the following procedure:

    1. Run the following command to create a test table with one field as the unique key or primary key.

      1. create table test(id int primary key, info text, crt_time timestamp);
    2. Update the data if it exists, or insert the data if it does not exist. The command is as follows.

      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. Run the following command to replace the variables, and perform a test. When a nonexistent value is inserted, only one session has the value successfully inserted, whereas the other session returns a primary key constraint error.

      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);
  • You can also use the WITH syntax in the following procedure:

    1. Run the following command to create a data table that can guarantee concurrency even without any primary key or unique constraint.

      1. create table test(id int, info text, crt_time timestamp);
    2. Perform either of the following operations to determine the result of updating the same data item when the specified record does not exist.

      • Data is inserted to only one session. When the same data item is updated, the session that is established first locks the record, whereas the session that is established later enters the waiting state. The procedure is as follows:

        1. Run the following command to determine the result of updating data:

          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. Replace the variables and perform a test.

          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)
      • Data is inserted to only one session. When the same data item is updated, the session that is established first updates the data, whereas the session that is established later returns an error directly. The procedure is as follows:

        1. Run the following command to determine the result of updating data:

          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. Replace the variables and perform a test.

          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)
Thank you! We've received your feedback.