PostgreSQL INSERT ON CONFLICT

This article describes how the PostgreSQL INSERT ON CONFLICT statement handles conflict situations when inserting rows.

PostgreSQL INSERT ON CONFLICT statements allow you to handle some data conflicts when inserting rows. If there is no conflict, insert rows normally, and if there is a conflict, the existing rows will be updated. That is to say, the INSERT ON CONFLICT statement implements the upsert function.

This INSERT ON CONFLICT statement was introduced in PostgreSQL 9.5.

PostgreSQL INSERT ON CONFLICT syntax

To implement the upsert function in PostgreSQL, use the INSERT ON CONFLICT statement as follows:

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT conflict_target conflict_action
[RETURNING {* | column_names}];;

Compared with the INSERT statement, there is only one more ON CONFLICT clause in INSERT ON CONFLICT.

In this syntax:

  • The conflict_target is the object where happends conflicts, which can be one of the following:

    • A column name. The column must be a primary key or unique index.
    • The ON CONSTRAINT constraint_name clause. The constraint_name must be the name of a unique constraint.
    • A WHERE clause.

  • The conflict_action is the action to take if there is a conflict, it can be one of the following:

    • DO NOTHING: If there is a conflict, take no action.
    • DO UPDATE: If there is a conflict, use DO UPDATE SET column_1 = value_1, .. WHERE condition to update the columns in the table.

PostgreSQL INSERT ON CONFLICT Examples

We are going to demonstrate the following example in the testdb database. Please use the following statement to create a database named testdb:

CREATE DATABASE testdb;

Connect to the testdb database as the current database:

\c testdb;

To demonstrate, use the following statement to create a new table, named users:

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  nickname VARCHAR(50) NOT NULL,
  login_name VARCHAR(50) UNIQUE,
  notes VARCHAR(255)
);

Here, the users table has id, nickname, login_name, and notes four columns, where login_name is a unique index column.

Insert some rows into the users table using the INSERT statement:

INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim', 'tim', 'This is Tim'),
    ('Tom', 'tom', 'This is Tom');

Insert a new row with a duplicate login_name of the existing row:

INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim2', 'tim', 'This is Tim2');

ERROR:  duplicate key value violates unique constraint "users_login_name_key"
DETAIL:  Key (login_name)=(tim) already exists.

Here, PostgreSQL gaven a error for the duplicate value.

You can try again using the INSERT ON CONFLICT statement to take some action if there are duplicate login_name. You can take two actions:

  • Use DO NOTHING to do nothing:

    INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim2', 'tim', 'This is Tim2')
ON CONFLICT (login_name) DO NOTHING;

    INSERT 0 0

    Here used the DO NOTHING option. Then, PostgreSQL returned normally and inserted 0 rows.

  • Use DO UPDATE to update the existing rows:

    INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim2', 'tim', 'This is Tim2')
ON CONFLICT (login_name)
    DO UPDATE SET nickname = 'Tim2', notes = 'This is Tim2'
RETURNING *;

    id | nickname | login_name |    notes
----+----------+------------+--------------
  1 | Tim2     | tim        | This is Tim2
(1 row)

    In the DO UPDATE clause, you can also use the EXCLUDED object to refer the data that caused the conflict. The above statement can be modified to the following statement using EXCLUDED:

    INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim2', 'tim', 'This is Tim2')
ON CONFLICT (login_name)
    DO UPDATE SET nickname = EXCLUDED.nickname,
                  notes = EXCLUDED.notes
RETURNING *;

    For the conflict objects, You can also use constraint names instead of column names. The above statement can use constraint names users_login_name_key instead of column names login_name:

    INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim3', 'tim', 'This is Tim3')
ON CONFLICT ON CONSTRAINT users_login_name_key
    DO UPDATE SET nickname = EXCLUDED.nickname,
                  notes = EXCLUDED.notes
RETURNING *;

    id | nickname | login_name |    notes
----+----------+------------+--------------
  1 | Tim3     | tim        | This is Tim3
(1 row)

Conclusion

PostgreSQL INSERT ON CONFLICT implements the upsert feature so that you can INSERT and UPDATE in one query.