Functions and usage of PostgreSQL UPSERT - Best Practices| Alibaba Cloud Documentation Center

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.

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 ]

Example of using UPSERT in PostgreSQL 9.5 and later

Run the following command to create a test table with one field as the unique key or primary key.
```
create table test(id int primary key, info text, crt_time timestamp);
```

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.

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)

Insert the data if it does not exist, or return the data directly without processing if it exists. The command is as follows.

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)

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.

  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)

Use the WITH syntax in the following procedure:

Run the following command to create a test table with one field as the unique key or primary key.
```
create table test(id int primary key, info text, crt_time timestamp);
```

Update the data if it exists, or insert the data if it does not exist. The command is as follows.

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);

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.
```
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:

Run the following command to create a data table that can guarantee concurrency even without any primary key or unique constraint.
```
create table test(id int, info text, crt_time timestamp);
```

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:

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

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);

Replace the variables and perform a test.

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)

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:

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

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);

Replace the variables and perform a test.

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)