conflict 冲突

最新推荐文章于 2024-05-27 10:04:50 发布

mnemosynefoxes

最新推荐文章于 2024-05-27 10:04:50 发布

阅读量1.9k

点赞数

本文链接：https://blog.csdn.net/sinat_40795878/article/details/79348126

版权

12 篇文章 0 订阅

订阅专栏

  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 } 

  [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] 

  where conflict_target can be one of: [ ON CONFLICT [ conflict_target ] conflict_action ] 

  ( { 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类似的功能。 

  用法举例: 

  创建测试表: 

  create table test(id int 
 primary key, info text, crt_time timestamp); 

  其中id为主键 

  插入数据: 

  insert into test 

  values ( 
 1,'test',now()) 

  on conflict (id) 

  do update set info= 
 excluded.info,crt_time= 
 excluded.crt_time; 

  第一次插入时,id不冲突,所以正常插入 

  再次插入数据: 

  insert into test 

  values ( 
 1,'hello digoal',now()) 

  on conflict (id) 

  do update set info=excluded.info,crt_time=excluded.crt_time; 

  此时,如果是没有写on conflict 语句,正常情况会包错. 

  但是这里写了之后,并没有报错,数据正常插入了. 

  但是查看表中数据会发现,只有最后一次插入的数据: 

  values (1,'hello digoal',now()) 

  之前插入的数据不见了. 

  所以成功执行了conflict 后的update语句 

关注