Label
PostgreSQL , upsert , insert on conflict do
background
PostgreSQL 9.5 introduces a new feature, UPSERT(insert on conflict do), which returns directly when an insert encounters a constraint error, or performs UPDATE instead.
The syntax is as follows
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 ]
Pre-PostgreSQL version 9.5 can implement UPSERT-like functions through functions or with grammar.
Examples of 9.5+ UPSERT usage
Create a test table with one field as a unique key or primary key.
create table test(id int primary key, info text, crt_time timestamp);
1. Insert if nonexistent, update if existent
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)
2. Insert if nonexistence exists, return directly if it exists (without any processing)
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)
9.5 - Examples of UPSERT usage
Users can use different methods according to their actual needs.
1. function
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)
2. WITH grammar, usage 1
create table test(id int primary key, info text, crt_time timestamp);
If it exists, it will be updated, and if it does not exist, it will be inserted.
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);
Replace variables for testing
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);
At the same time, insert a non-existent value. Only one session succeeds and the other session reports a PK constraint error.
3. WITH grammar, usage 2
Concurrency can be guaranteed even if the table has no PK or unique constraints.
create table test(id int, info text, crt_time timestamp);
3.1 For the record does not exist, it can ensure that only one session inserts data. For the same data update, the first session lock s the record, and the subsequent session wait s.
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 variables for testing
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)
3.2 For the record does not exist, it can ensure that only one session inserts data. For the same data update, the first session updates the data, and the subsequent session does not wait and fails directly.
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 variables for testing
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)