Postgresql insert on conflict笔记

描述#

针对数据写入时有主键冲突的情况，INSERT ON CONFLICT语法可以将冲突主键的INSERT行为转换为UPDATE行为，从而实现冲突主键的覆盖写入。该特性又称UPSERT覆盖写，与MySQL的REPLACE INTO类似。

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
其中，conflict_target为：
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
其中，conflict_action为：
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ] 

ON CONFLICT子句可以实现覆盖写入。该子句由conflict_target和conflict_action组成。

参数	说明
conflict_target	conflict_action取值为Do Update时，conflict_target需要指定用来定义冲突的主键列或唯一索引列。conflick_action取值为Do Nothing时，conflict_target可省略。
conflict_action	用于指定冲突后需要执行的动作。取值说明：DO NOTHING：如果conflict_target指定的列有冲突，则丢弃待插入的数据。DO UPDATE：如果conflict_target指定的列有冲突，则按照后面的UPDATE子句进行数据覆盖。

创建测试表#

CREATE TABLE jruing.user_info (
    "name" varchar(255),
    "age" int4,
    "sex" varchar(255),
    "inserttime" date,
    constarint "user_info_pk" primary key ("name")
)

新增测试数据#

insert into table_name(name, age, sex,inserttime) values('jruing','男',27,'2023-05-10 00:00:00') 

insert 新增时忽略已存在的数据#

insert into table_name(name, age, sex,inserttime) values('jruing',27,'男','2023-05-10 00:00:02') on conflict(name) do nothing

insert 有则更新，无则插入#

insert into table_name(name, age, sex, inserttime) values ('jruing',27,'男','2023-05-10 00:00:02') on conflict(name) do update set inserttime=EXCLUDED.inserttime

注意事项#

confilct中包含的字段必须为冲突的列名（冲突是指违反主键约束，唯一约束等其他约束）