Postgres ON CONFLICT是PostgreSQL数据库中的一个功能,用于处理插入或更新数据时的冲突情况,下面这篇文章主要给大家介绍了关于PostgreSQL中ON CONFLICT的使用及一些扩展用法的相关资料,需要的朋友可以参考下

在 PostgreSQL 中，ON CONFLICT 子句是用在 INSERT 语句中的一种机制，它可以帮助你处理当插入操作遇到违反唯一性约束（比如唯一索引或主键约束）时的情况。使用 ON CONFLICT 子句，你可以指定当违反唯一性约束时应该采取的操作，比如忽略这个插入，或者更新已经存在的行。

ON CONFLICT (sample_id_lims) DO UPDATE 是指当你在插入数据时，如果 sample_id_lims 字段的值导致了唯一性约束的冲突，那么不是放弃这次插入，而是更新已经存在的那一行数据。

这里有一个具体的例子：

假设你有一个名为 samples 的表，它有一个名为 sample_id_lims 的字段，该字段上有一个唯一索引。现在，你想插入一个新的样本数据，但如果 sample_id_lims 的值已经存在，你希望更新这条记录的其他字段而不是放弃插入。

1

2

3

4

5


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' 的记录已经存在，那么 ON CONFLICT 子句会触发，并执行 DO UPDATE 操作。SET 子句用于更新冲突行的 data_field1 和 data_field2 字段。关键字 EXCLUDED 用来引用那些原本尝试插入但发生冲突的值。
通过使用 ON CONFLICT 子句，你可以确保表中的数据保持唯一性，同时仍然可以通过更新操作来应对重复的插入尝试。

ON CONFLICT 子句在 PostgreSQL 中的使用不仅限于简单的 UPDATE 操作。这里有一些扩展用法：

1、指定唯一约束名称:如果表中有多个唯一约束，你可以通过唯一约束的名称指定应对哪个约束的冲突。

1

2

3


INSERT INTO table_name (column1, column2) VALUES (value1, value2)

ON CONFLICT ON CONSTRAINT constraint_name

DO NOTHING; -- 或者 DO UPDATE ...


2、条件更新:在执行 UPDATE 操作时，可以加入 WHERE 子句来设置条件，仅在满足某些条件时才更新。

1

2

3

4


INSERT INTO table_name (column1, column2) VALUES (value1, value2)

ON CONFLICT (column1) DO UPDATE

SET column2 = EXCLUDED.column2

WHERE table_name.column3 > 10;


3、使用 DO NOTHING:如果你不想在发生冲突时执行任何操作，可以使用 DO NOTHING。这样，如果插入的数据违反了唯一性约束，PostgreSQL 会忽略这个插入，并且不会报错。

1

2


INSERT INTO table_name (column1, column2) VALUES (value1, value2)

ON CONFLICT (column1) DO NOTHING;


4、返回插入或更新后的行:通过 RETURNING 子句，你可以在 INSERT 操作完成后返回插入或更新的行的信息。

1

2

3

4


INSERT INTO table_name (column1, column2) VALUES (value1, value2)

ON CONFLICT (column1) DO UPDATE

SET column2 = EXCLUDED.column2

RETURNING *;


5、使用 WHERE 子句过滤冲突的行:你可以在 ON CONFLICT 子句中使用 WHERE 来过滤哪些冲突行应该被更新。

1

2

3


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' 时，才会执行 UPDATE 操作。

使用 ON CONFLICT 子句可以帮助你以一种优雅的方式处理可能的数据插入冲突，确保数据的完整性，同时还能灵活地进行各种条件处理。

on conflict 用法真的很方便，不存在就插入，存在可以更新 可以do nothing，就是用起来要注意几点：

  • 字段必须完全同名。试了半天不行，改了同名就行。
  • 拿不到原表的内容。退而求其次用原表做一个内连接就行，取原值然后想怎么玩都行。下面这个例子就是取原值并加上新统计值更新回原表。
  • 建临时表可以加入很多自己需要的内容，操作空间更大
  • excluded代表新选择出来的内容。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23


insert into plate_no_info 

select plate_no,total_orders ,first_parking_time,last_parking_time

from (

select t1.plate_no,(plate_no_info.total_orders + cnt ) as total_orders,plate_no_info.first_parking_time,t1.last_parking_time

from (

select plate_no,count(id) cnt,min(parking_time) first_parking_time,max(parking_time) last_parking_time

from order_list

WHERE created_at > CURRENT_DATE - 1

and plate_no ~'^[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'

GROUP BY plate_no

) t1,

plate_no_info

WHERE 1=1

AND t1.plate_no = plate_no_info.plate_no

)

as t_temp

on conflict(plate_no)

--  do NOTHING

do update

set

total_orders = excluded.total_orders,

first_parking_time = excluded.first_parking_time,

last_parking_time = excluded.last_parking_time;