PostgreSQL中ON CONFLICT的使用及一些扩展用法例子解析

代码示例：

在PostgreSQL中，ON CONFLICT子句是INSERT语句的一部分，它允许你在尝试插入违反唯一性约束的数据时执行特定的操作。以下是一些ON CONFLICT的基本用法和扩展用法的例子：

基本用法

1. 更新现有记录：

   INSERT INTO samples (sample_id_lims, data_field1, data_field2)
   VALUES ('123', 'New Data 1', 'New Data 2')
   ON CONFLICT (sample_id_lims) DO UPDATE
   SET data_field1 = EXCLUDED.data_field1,
       data_field2 = EXCLUDED.data_field2;
   

   如果sample_id_lims为’123’的记录已经存在，则更新该记录的其他字段。

2. 指定唯一约束名称：

   INSERT INTO table_name (column1, column2) VALUES (value1, value2)
   ON CONFLICT ON CONSTRAINT constraint_name DO NOTHING;
   

   如果表中有多个唯一约束，可以通过约束名称来指定处理哪个约束的冲突。

3. 条件更新：

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

   只有当冲突行的column3值大于10时，才更新column2。

4. 什么都不做：

   INSERT INTO table_name (column1, column2) VALUES (value1, value2)
   ON CONFLICT (column1) DO NOTHING;
   

   如果插入的数据违反了唯一性约束，PostgreSQL会忽略这个插入。

5. 返回插入或更新后的行：

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

   使用RETURNING子句可以在插入或更新操作后返回受影响的行的信息。

扩展用法

1. 使用WHERE子句过滤冲突的行：

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

   只有当冲突行的status字段为’active’时，才会执行更新操作。

2. 使用子查询插入并处理冲突：

   INSERT INTO t1 SELECT * FROM t2 ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);
   

   将表t2的数据插入表t1，如果主键冲突，则覆盖写入非主键的列。

3. 插入多条数据并处理冲突：

   INSERT INTO t1 VALUES (0,0,0,0), (1,1,1,1) ON CONFLICT DO NOTHING;
   

   插入两行数据，其中主键冲突的行不进行任何操作，主键不冲突的行正常插入。

4. 组合键处理：
   如果表有组合唯一索引，可以这样处理冲突：

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

   如果column1和column2的组合值与现有记录冲突，则更新column3。

5. 属性自增：
   在冲突时，可以对某个属性进行自增操作：

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

   如果column1的值与现有记录冲突，则将column2的值增加1。

这些例子展示了ON CONFLICT子句的灵活性和强大功能，可以帮助你优雅地处理数据插入冲突，确保数据的完整性，同时还能灵活地进行各种条件处理。

喜欢本文，请点赞、收藏和关注！