PostgreSQL ON CONFLICT冲突时进行额外操作
作者:mmseoamin日期:2023-12-21

前言

使用ON CONFLICT需设置键,否则报错“没有匹配ON CONFLICT说明的唯一或者排除约束”。


PostgreSQL的ON CONFLICT子句提供了多种冲突处理类型,用于在插入操作中处理冲突。以下是常见的几种类型:

  1. DO NOTHING:在发生冲突时不执行任何操作,保持原有数据不变。
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO NOTHING;

组合键示例, column1、column2需组合键

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1, column2) DO NOTHING;
  1. DO UPDATE SET:在发生冲突时执行更新操作,可以指定要更新的字段和更新的值。

EXCLUDED是一个特殊的关键字,用于引用插入操作中的冲突行。此处冲突时,column2的值更新为value2

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = EXCLUDED.column2;

这是一个冲突时属性自增的示例,不受传入的value2的值的影响

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = table_name.column2 + 1;

更新多个值示例,column3是table_name中的属性

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = table_name.column2 + 1, column3 = table_name.column3 - 1;
  1. DO UPDATE WHERE:在发生冲突时执行更新操作,并根据条件进行更新。
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = EXCLUDED.column2 WHERE column2 < EXCLUDED.column2;
  1. DO NOTHING RETURNING:在发生冲突时不执行任何操作,若不冲突返回插入的行。
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO NOTHING RETURNING *;
  1. DO UPDATE SET ... WHERE ... RETURNING:在发生冲突时执行更新操作,并根据条件进行更新,并返回更新的行。
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = EXCLUDED.column2 WHERE column2 < EXCLUDED.column2 RETURNING *;