551
PostgreSQL upsert功能(insert on conflict do)的用法
標簽
PostgreSQL , upsert , insert on conflict do
背景
PostgreSQL 9.5 引入了一項新功能，UPSERT(insert on conflict do)，當插入遇到約束錯誤時，直接返回，或者改為執行UPDATE。
語法如下
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 ]
PostgreSQL 9.5以前的版本，可以通過函數，或者with語法來實現UPSERT類似的功能。
9.5+ UPSERT用法舉例
創建一張測試表，其中一個字段為唯一鍵或者主鍵。
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;
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. 不存在則插入，存在則直接返回(不做任何處理)
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- UPSERT用法舉例
用戶可以根據實際需求，使用不同的方法
1. 函數
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語法，用法1
create table test(id int primary key, info text, crt_time timestamp);
存在則更新，不存在則插入。
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);
替換變量，進行測試
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);
同時插入一條不存在的值，隻有一個會話成功，另一個會話會報PK約束錯誤。
3. WITH語法，用法2
即使表沒有PK或者唯一約束，也能保證並發。
create table test(id int, info text, crt_time timestamp);
3.1 對於記錄不存在，可以保證隻有一個session插入數據，對於同一條數據更新，先來的session會lock著記錄，後來的session會wait。
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);
替換變量，進行測試
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 對於記錄不存在，可以保證隻有一個session插入數據，對於同一條數據更新，先來的session會更新數據，後來的session不等待，直接失敗。
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);
替換變量，進行測試
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) 最後更新：2017-04-24 21:32:59