Postgresql插入或更新操作upsert
幂等性的一个要求是多次操作的结果一致。对于update操作,多次直接的结果都是最后update的值,是满足需求的。但对于insert,如果已经插入,第二次会报错,duplicate error, 主键重复或者unique key duplicate。所以需要做一下处理。 最简单的就是,try-catch,当报错的时候,调用update去更新,或者策略更简单点,直接返回就行,不需要更新,以第一条为准。 PostgreSQL从9.5之后就提供了原子的upsert语法: 不存在则插入,发生冲突可以update。 ## Inert语法 官方文档: https://www.postgresql.org/docs/devel/sql-insert.html [ 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 ] index_column_name The name of a table_name column. Used to infer arbiter indexes. Follows CREATE INDEX format. SELECT privilege on index_column_name is required. index_expression Similar to index_column_name,but used to infer expressions on table_name columns appearing within index definitions (not simple columns). Follows CREATE INDEX format. SELECT privilege on any column appearing within index_expression is required. ## 使用示例 创建表 当主键id冲突时,更新其他字段
当主键或者unique key发生冲突时,什么都不做 INSERT INTO test.upsert_test(id,"name") VALUES(1,'m'),'n'),'c') ON conflict(id) DO NOTHING; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |