Postgresql插入或更新操作upsert

落日映苍穹つ 2023-10-06 20:08 14阅读 0赞

幂等性的一个要求是多次操作的结果一致。对于update操作，多次直接的结果都是最后update的值，是满足需求的。但对于insert，如果已经插入，第二次会报错，duplicate error， 主键重复或者unique key duplicate。所以需要做一下处理。

最简单的就是，try-catch，当报错的时候，调用update去更新，或者策略更简单点，直接返回就行，不需要更新，以第一条为准。

PostgreSQL从9.5之后就提供了原子的upsert语法： 不存在则插入，发生冲突可以update。

### Inert语法 ###

官方文档： [PostgreSQL: Documentation: devel: INSERT][PostgreSQL_ Documentation_ devel_ INSERT]

\[ 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.

### 使用示例 ###

创建表

CREATE TABLE "test"."upsert\_test" ( "id" int4 NOT NULL, "name" varchar(255) COLLATE "pg\_catalog"."default" ) ;

当主键id冲突时，更新其他字段

INSERT INTO test.upsert\_test(id, "name") VALUES(1, 'm'),(2, 'n'),(4, 'c') ON conflict(id) DO UPDATE SET "name" = excluded.name;

*  did 冲突的主键
 *  EXCLUDED 代指要插入的记录

当主键或者unique key发生冲突时，什么都不做

INSERT INTO test.upsert\_test(id, "name") VALUES(1, 'm'),(2, 'n'),(4, 'c') ON conflict(id) DO NOTHING;

[PostgreSQL_ Documentation_ devel_ INSERT]: https://www.postgresql.org/docs/devel/sql-insert.html