PostgreSQL中如果某个字段存在唯一约束,当我们再次使insert插入时,就会报错。所以我们需要处理,最简单的就是当报错时,就去调用update去更新。但对于PostgreSQL从9.5后就提供了upsert操作,不存在插入,存在就更新。

insert语句

官方文档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[ 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 ]

upsert使用

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
create table test
(
id bigint not null,
name varchar(255) default ''::character varying not null,
remark varchar(255) default ''::character varying not null,
created_at bigint default '0'::bigint not null,
updated_at bigint default '0'::bigint not null,
deleted_at bigint default '0'::bigint not null,
primary key (id, deleted_at)
);

create unique index test_uq_name
on test (name, deleted_at);

可以看到字段name,deleted_at为唯一索引。

插入数据

1
INSERT INTO tes(id, name) VALUES(1, 'm', '111', 1644387915, 1644387915, 0),(2, 'n', '222', 1644387915, 1644387915, 0),(4, 'c', '333', 1644387915, 1644387915, 0);

当我们再插入以下数据时,就会报错

1
INSERT INTO tes(id, name) VALUES(3, 'm', 1644389939, 1644389939, 0);

这是我们就可以使用upsert

1
INSERT INTO tes(id, name) VALUES(3, 'm', 1644389939, 1644389939, 0) ON CONFLICT(name, deleted_at) DO UPDATE SET remark = excluded.remark;

当存在冲突时,更新remark字段,excluded代表要插入的记录。

也可以在冲突时什么都不做

1
INSERT INTO tes(id, name) VALUES(3, 'm', 1644389939, 1644389939, 0) ON CONFLICT(name, deleted_at) DO NOTHING;