How to execute an UPSERT with PostgreSQL

In cases where you do not want to handle unique constraint violation errors that are caused by duplicate entries, an UPSERT would be useful to have with PostgreSQL. An UPSERT is similar to an INSERT INTO ... IF NOT EXISTS.

In the following example, the users table has a primary key id and a name. By executing the following statement twice ...

INSERT INTO users (id, name) VALUES ('fbdf0e604e', 'jonas.havers');

... you will get a unique constraint violation error similar to this:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint [...]

The PostgresSQL INSERT documentation specifies an ON CONFLICT clause as an alternative to raising a unique constraint or exclusion constraint violation error. There are two options from which you can choose:

  • ON CONFLICT DO NOTHING
  • ON CONFLICT DO UPDATE

When you use one of them, you won't get the duplicate key error again:

INSERT INTO users (id, name) VALUES ('fbdf0e604e', 'jonas.havers') ON CONFLICT DO NOTHING;

ON CONFLICT DO is similar to an UPSERT in the sense that:

  • With DO NOTHING, no row will be inserted when there is a conflict with an existing row.
  • With DO UPDATE, you can specify the UPDATE statement that should be executed on the existing row that conflicts with the row proposed for insertion, e.g.
INSERT INTO users (id, name) VALUES ('fbdf0e604e', 'jonas.havers') ON CONFLICT DO UPDATE SET name='jonas.havers';