批量插入数据表数据时,主键冲突的解决

 

 

2、使用普通的insert into on conflict合并写入,存在写入放大思路:

大量数据,批量插入到数据表中时,很容易造成主键冲突,重复数据有唯一约束插入不进去表中,报错的问题出现。

排查错误,去找某条数据,在大量的数据,大量的批处理或者单条执行的sql语句中找数据也是不现实的。

所以思路就是在插入的过程中,不仅单单插入,同时判断是否主键或唯一冲突。若冲突,则将插入操作改成对重复数据的更新操作。

 

PostgreSQL , 合并写 , insert on conflict , 不必要更新

 

 

例子:

1,如下面的一张表,往下面的一张表中插入数据

create table tbl(  
  c1 int,   
  c2 int,   
  c3 int,   
  c4 int,   
  c5 timestamp,   
  unique (c1,c2)  
);  

2、使用普通的insert into on conflict合并写入,存在写入放大 

insert into tbl   
  select id,id,1,random(),now() from generate_series(1,1000000) t(id)   
  on conflict(c1,c2)   
  do update   
  set   
  c3=excluded.c3,c4=excluded.c4,c5=excluded.c5; 

每一次操作都会更新所有记录

3、优化方法,加入更新条件,避免未变化的记录被更新

例如当c3,c4没有变化时,不更新。

  where  
  tbl.c3 is distinct from excluded.c3 or  
  tbl.c4 is distinct from excluded.c4;  

 

完整SQL如下 :

insert into tbl   
  select id,id,1,random(),now() from generate_series(1,1000000) t(id)   
  on conflict(c1,c2)   
  do update   
  set   
  c3=excluded.c3,c4=excluded.c4,c5=excluded.c5  
  where  
  tbl.c3 is distinct from excluded.c3 or  
  tbl.c4 is distinct from excluded.c4;  

 

此时每次更新的就是那些真正发生了变化的记录 

 

 

[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    [ OVERRIDING { SYSTEM | USER} VALUE ]  
    { 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 [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ]  

is distinct from是一种不等于的用法,其中包括NULL值的处理(认为null is not distinct from null is TRUE)。两个NULL值返回FALSE,一个NULL值返回TRUE。

null is distinct from null 返回false

null is distinct from nonnull 返回true

nonnull is distinct from nonnull 根据实际的VALUE来判断是否相等,相等返回false,不相等返回true。

当要判断NULL时,这个比=操作符好用,使用=等操作符时,通常底层函数是strict的,所以当操作数包含NULL值时,操作符的结果也返回NULL而不是我们想要的true或false。

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值