PostgreSQL插入或者更新

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

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 ]

upsert使用

创建表

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);