Updated May 31, 2023
Introduction to PostgreSQL ON CONFLICT
PostgreSQL on conflict is used to insert the data in the same row twice, which the constraint or column in PostgreSQL identifies values. If we want to insert data into the same column twice at the same time, we have to use the conflict by using an insert statement in PostgreSQL. It will happen when we don’t duplicate any input before passing it to the Postgres table.
Syntax
Insert into name_of_table (name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN) values (values_of_column1, values_of_column2, values_of_column3, …, value_of_columnN) ON conflict target action;Below is the parameter description syntax of on conflict in PostgreSQL.
- Insert into –This is defined as inserting a row into the table using the conflict statement in PostgreSQL.
- Name of the table –This is defined as the name of the table we used on the conflict statement at the time of insertion.
- Name of column 1 to the name of column N –This is defined as a defined column at the time of value insertion into the table using conflict. Thus, we can select multiple columns at the same time to insert values into the table.
- Value of column 1 to the value of column N –This is defined as defining column value at the time of insertion into the table using conflict. Thus, we can define value as per how many columns we have defined at the time of insertion.
- On conflict –This statement is used in PostgreSQL to insert data into the same row twice.
- Target –We can define target value with on conflict statement. The target value in the conflict statement can be a constraint name.
- Action – Action refers to the specific action taken using the ON CONFLICT statement in PostgreSQL.
How ON CONFLICT statement works in PostgreSQL?
- Below is the working conflict statement in PostgreSQL.
- We have used conflict statements with insert and update statements in PostgreSQL.
- If we want to change any column name data with other names at the same time, we use on conflict statement with the insert statement.
- If we must restore company information and change the mail, we must use it on the conflict statement.
- We can use a constraint name on the conflict statement in PostgreSQL and a column name on the conflict statement.
- If we have concatenated old column data with new column data that already existed in the table at the same time, we have used an update clause with insert and on conflict statement.
- While using on conflict with the do-nothing, it will avoid inserting a row from its alternative action.
- While using on conflict with doing an update, it will update the existing rows from the table, which conflicted with the insertion from the table.
- Conflict action and conflict target are very useful and important parameters when using on conflict statements in PostgreSQL.
- An update on conflicts automatically guarantees the update and insert, providing an independent error.
- In PostgreSQL, users commonly refer to the process of merging databases as an upsert. This is because when we have inserted a new row into the table, PostgreSQL updates the row that already existed in the table.
- Using upsert PostgreSQL will update the row if it already exists in the table; otherwise, it will insert a new row into the table.
- While using the feature of upsert, we have used on conflict and insert statements together.
- We can use the insert on conflict statement in PostgreSQL to support the upsert feature.
Examples
Below is an example of on conflict statement. We use a conflict_test table to describe an example of on conflict in PostgreSQL.
Below is the table and data description of the conflict_test table.
select * from conflict_test;
\d+ conflict_test;Output:
Example #1
On conflict statement with constraint name as the target.
The below example shows that on conflict statement with the target as constraint name.
We are using “conflict_test_stud_name_key” as a constraint with on conflict statement.
INSERT INTO conflict_test (stud_name, stud_email) VALUES ('ABC', '[email protected]') ON CONFLICT ON CONSTRAINT conflict_test_stud_name_key DO NOTHING;
select * from conflict_test;Output:
Example #2
On conflict statement with column name as the target.
The below example shows that on conflict statement with the target as a column name.
We are using stud_name as a column name with on conflict statement.
INSERT INTO conflict_test (stud_name, stud_email) VALUES ('ABC', '[email protected]') ON CONFLICT (stud_name) DO NOTHING;
select * from conflict_test;Output:
Example #3
On conflict statement with update statement as action.
The below example shows that on conflict statement with update statement as action in PostgreSQL.
We are using stud_name as a column name with on conflict statement. Also, we are using stud_email with an update statement.
The example below demonstrates the addition of the email ID [email protected] to the stud_name as ABC.
INSERT INTO conflict_test (stud_name, stud_email) VALUES ('ABC', '[email protected]') ON CONFLICT (stud_name) DO UPDATE SET stud_email = EXCLUDED.stud_email || ';' || conflict_test.stud_email;
select * from conflict_test;Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL ON CONFLICT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
