Postgres multiple on conflict clauses

by

PostgreSQL Multiple ON CONFLICT Clauses

In PostgreSQL, the ON CONFLICT clause is used in the INSERT statement to handle conflicts that arise when inserting data into a table that violates a unique constraint. Normally, when a conflict occurs, the insert operation would fail and raise an error. However, with the ON CONFLICT clause, you can define alternative actions to be taken in case of a conflict.

The ON CONFLICT clause has various options, including DO NOTHING, DO UPDATE, and DO NOTHING with the WHERE condition. Let’s explore these options with examples:

1. DO NOTHING:

The DO NOTHING option tells PostgreSQL to do nothing and skip the conflicted rows, allowing the insert operation to continue.

INSERT INTO employees (id, name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Alice')
    ON CONFLICT (id) DO NOTHING;

In this example, we are inserting multiple rows into the “employees” table with the ID and Name columns. If any of the ID values already exist in the table, the conflicting rows will be ignored, and the rest of the rows will be inserted.

2. DO UPDATE:

The DO UPDATE option allows you to specify which columns should be updated when a conflict occurs.

INSERT INTO employees (id, name, salary) VALUES (1, 'John', 5000), (2, 'Jane', 6000), (3, 'Alice', 7000)
    ON CONFLICT (id) DO UPDATE SET name = excluded.name, salary = excluded.salary;

In this example, we are inserting multiple rows into the “employees” table with the ID, Name, and Salary columns. If any of the ID values already exist in the table, the conflicting rows will be updated with the provided Name and Salary values. The “excluded” keyword refers to the new row being inserted that caused the conflict.

3. DO NOTHING with WHERE condition:

The DO NOTHING option can also be combined with a WHERE condition to further customize the behavior when a conflict occurs.

INSERT INTO employees (id, name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Alice')
    ON CONFLICT (id) DO NOTHING WHERE employees.salary > 5000;

In this example, we are inserting multiple rows into the “employees” table with the ID and Name columns. If any of the ID values already exist in the table and the corresponding salary is greater than 5000, then the conflicting rows will be ignored and skipped.

These are the basic examples of using multiple ON CONFLICT clauses in PostgreSQL. Experiment with these clauses to handle conflicts effectively and customize your insert operations as per your application requirements!

Leave a comment