SoFunction
Updated on 2025-04-07

The use of ON CONFLICT in PostgreSQL and some extension usages

In PostgreSQL, the ON CONFLICT clause is a mechanism used in INSERT statements that helps you deal with situations when insert operations encounter unique constraints (such as unique indexes or primary key constraints). Using the ON CONFLICT clause, you can specify actions that should be taken when a unique constraint is violated, such as ignoring this insertion, or updating an existing row.

ON CONFLICT (sample_id_lims) DO UPDATE refers to when you insert data, if the value of the sample_id_lims field causes a conflict of unique constraints, then instead of giving up this insertion, the existing row of data is updated.

Here is a specific example:

Suppose you have a table named samples which has a field named sample_id_lims with a unique index on it. Now you want to insert a new sample data, but if the value of sample_id_lims already exists, you want to update the other fields of the record instead of giving up the insertion.

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;

In this example, if the record with sample_id_lims '123' already exists, the ON CONFLICT clause will fire and perform the DO UPDATE operation. The SET clause is used to update the data_field1 and data_field2 fields of the conflicting row. The keyword EXCLUDED is used to refer to values ​​that were originally attempted to be inserted but conflicted.
By using the ON CONFLICT clause, you can ensure that the data in the table remains unique while still being able to deal with duplicate insertion attempts with update operations.

The use of the ON CONFLICT clause in PostgreSQL is not limited to simple UPDATE operations. Here are some extension usages:

1. Specify a unique constraint name:If there are multiple unique constraints in the table, you can specify which constraint conflict to deal with by the name of the unique constraint.

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

2. Condition update:When performing a UPDATE operation, you can add a WHERE clause to set the conditions and update them only when certain conditions are met.

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

3. Use DO NOTHING:If you don't want to do anything in the event of a conflict, you can use DO NOTHING. In this way, if the inserted data violates the uniqueness constraint, PostgreSQL will ignore the insertion and will not report an error.

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

4. Return to the inserted or updated line:The RETURNING clause allows you to return information about the inserted or updated rows after the INSERT operation is completed.

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

5. Use the WHERE clause to filter conflicting lines:You can use WHERE in the ON CONFLICT clause to filter which conflicting rows should be updated.

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;

In the example above, the UPDATE operation is performed only if the status field of the conflict line is 'active'.

Using the ON CONFLICT clause can help you handle possible data insertion conflicts in an elegant way, ensuring data integrity while also providing flexibility in various conditional processing.

The usage of on conflict is really convenient. If it does not exist, it can be inserted. If it exists, it can be updated. It can do nothing, which means you should pay attention to when using it.：

  • The fields must be exactly the same name. I tried for a long time but it didn't work, just change the same name.
  • Can't get the contents of the original table. Just use the original table to make an internal connection, take the original value and play whatever you want. The following example is to take the original value and add the new statistical value to update it back to the original table.
  • Create a temporary table to add a lot of content you need, making the operation space larger
  • Excluded represents newly selected content.
	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;

Summarize

This is the end of this article about the use of ON CONFLICT in PostgreSQL and some extension usage. For more related content on ON CONFLICT in PGSQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!