This article is half-done without your Comment! *** Please share your thoughts via Comment ***
After a long time of waiting, PostgreSQL 9.5 introduced INSERT ON CONFLICT [DO UPDATE] [DO NOTHING].
This option basically helps to perform DML actions like, Insert IF not Exists, Update IF Exists.
Previously, we have to use upsert or merge statement to do this kind of operation. I have also published an article on it.
PostgreSQL: Insert – Update or Upsert – Merge using writable CTE
This newly option has two varieties:
- INSERT ON CONFLICT DO UPDATE: If record matched, it is updated with the new data value.
- INSERT ON CONFLICT DO NOTHING: If record matched, it skips the record or error.
Below is a full demonstration of this:
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_Employee ( EmpID INT PRIMARY KEY ,EmpName CHARACTER VARYING ); INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Roy'),(3,'Lee') ,(4,'Nivu'),(5,'Rajesh'),(6,'Nupur'); |
Insert one more row with option INSERT ON CONFLICT DO UPDATE:
Using this option, if a conflict occurs then it will update the mentioned data.
Here, I have use “Excluded” table which is special table and contains the row-to-be-inserted.
|
1 2 3 4 |
INSERT INTO tbl_Employee VALUES (7,'Ramu') ON CONFLICT (EmpID) DO UPDATE SET EmpName = Excluded.EmpName; |
|
1 2 3 4 |
INSERT INTO tbl_Employet VALUES (7,'Mahi') ON CONFLICT (EmpID) DO UPDATE SET EmpName = Excluded.EmpName; |
Using this option, if conflict occurs then it will not take any action or any error.
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO tbl_Employee VALUES (8,'Noor') ON CONFLICT (EmpID) DO NOTHING; INSERT INTO tbl_Employee VALUES (8,'Noor') ON CONFLICT (EmpID) DO NOTHING; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT *FROM tbl_Employee; /* EmpID EmpName ----------------- 1 Anvesh 2 Roy 3 Lee 4 Nivu 5 Rajesh 6 Nupur 7 Mahi 8 Noor */ |
