美文网首页
PG的Upsert语法多种实现

PG的Upsert语法多种实现

作者: 蠟筆小噺没有烦恼 | 来源:发表于2020-05-30 22:15 被阅读0次

本文使用以下表做测试例子

create $tableName (id varchar(255),name varchar(255),age bigint,update timestamp);

1 版本9.5 之后

insert on conflict do)。当插入遇到约束错误时,直接返回或者改为执行UPDATE。

insert into $tableName (id,name,age,update) values('id1','name1',18,now()) ON conflict(id,name) DO UPDATE SET age = EXCLUDED.age,update=EXCLUDED.update

如果希望在插入冲突之后什么都不做:

insert into $tableName (id,name,age,update) values('id1','name1',18,now()) ON conflict(id,name) DO NOTHING  ;

2 版本9.5 之前

2.1 使用存储过程

构造存储过程函数

create or replace function $functionName(f_id varchar(255) , f_name varchar(255),f_age bigint) returns void as $$  
declare  
    res int;  
begin  
    update $tablename set age=18,update=now() where id=f_id and name=f_name;  
if not found then  
  insert into $tablename (id,name,age,update) values (f_id,f_name,18,now());  
end if;  
exception when others then  
  return;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  

调用函数
select functionName(id,name,age)

2.2 使用with

若数据存在则更新,不存在则插入。

with upsert as (update $tablename set age=$age,update=now() where id=$id name=$name  returning *) insert into $tablename select $id,$name,$age,now() where not exists (select 1 from upsert where id=$id and name=$name);

3 性能对比

使用了一个简单的表作为测试:

create table  test (pid bigint,date date, hour smallint,type varchar(8),num bigint, update timestamp default now(),primary key(pid,date,hour,type));

对每种执行方式尝试3次,取平均值。

条件\语法 onConflict语法 函数过程语法 with语法
只插入 10w 10s 9s 33s
只更新 10w 11s 11s 35s
插入\更新穿插 10w 10s 10s 38s
只插入 20w 16s 16s 70s
只更新 20w 16s 30s 60s
插入\更新穿插 20w 18s 22s 72s

嗯,测试比较简陋,也不太严格,但是可以看出大致的使用优势。

  • with语法性能最差,无论更新还是插入
  • 无论哪种语法,更新和插入的时间总是很相近的
  • onConflict比函数过程更加稳定

所以with语法不推荐使用;在PG9.5之后的版本,还是使用onConflict语法吧,毕竟语法简单,更不容易出错。
PG9.5之前版本建议使用函数过程。注意必须对每个表单独设置函数。但是函数定义语法较繁琐。

祝好运!

相关文章

网友评论

      本文标题:PG的Upsert语法多种实现

      本文链接:https://www.haomeiwen.com/subject/nahkzhtx.html