Menu

PostgreSQL UPSERT with ON CONFLICT

Use PostgreSQL INSERT ON CONFLICT to skip duplicate rows or update them, with conflict targets, EXCLUDED, and RETURNING examples.

Updated on

PostgreSQL uses INSERT ... ON CONFLICT for upsert behavior: insert a proposed row, or take an alternative action if a unique constraint or index conflicts. PostgreSQL introduced this syntax in version 9.5. The current INSERT reference describes it as an atomic insert-or-update operation when DO UPDATE is used.

Syntax

The following is a simplified form of the syntax used in the examples:

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT [conflict_target] conflict_action
[RETURNING * | output_expression [AS output_name]];

A conflict_target identifies the unique index or constraint that triggers the alternative action. Common forms are a list of columns or expressions, optionally followed by an index predicate, or ON CONSTRAINT constraint_name. It is not a standalone WHERE clause.

The conflict_action is one of these:

  • DO NOTHING: skip the conflicting row. The target is optional; when omitted, PostgreSQL handles conflicts for all usable unique constraints and indexes.
  • DO UPDATE SET column = expression: update the conflicting row. DO UPDATE requires a target. The optional WHERE condition after the SET list controls whether the conflicting row is updated.

The two WHERE positions have different purposes: a predicate in the conflict target helps infer a partial unique index, while a predicate after DO UPDATE SET filters the update action. For more details, see the official conflict-target and conflict-action syntax.

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.

PostgreSQL reports a unique-constraint violation for the duplicate value; see PostgreSQL Error 23505 troubleshooting.

For how to find the conflicting row and distinguish SQLSTATE 23505 from an intentional upsert, see PostgreSQL Error 23505 troubleshooting.

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

    The DO NOTHING action handled the conflict without an error, so the command 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.

For SQL Server’s transaction-based pattern and MERGE trade-offs, see SQL Server UPSERT.

To compare PostgreSQL’s conflict-target syntax with other database patterns, see SQL UPSERT Syntax by Database.