Upsert in PostgreSQL Using ON CONFLICT

If you’re using Postgres and need to make an update to a row OR create a new row if one doesn’t exist, new versions of PostgreSQL make this a lot easier. In other databases this concept is usually referred to as upsert which is short for update / insert. Let’s see a full example, then we’ll break it down by sections.

1
2
3
4

INSERT INTO users
VALUES ('marcus','adams', 'm.adams@email.com')
ON CONFLICT (email_address)
DO UPDATE SET last_updated = Date.now()

The breakdown

1
2

INSERT INTO users
VALUES ('marcus','adams', 'm.adams@email.com')

This is a normal insert statement, we’re inserting values into a table called users. Always, always, always sanitize input before sending it to your database.

1
2

ON CONFLICT (email_address)
DO UPDATE SET last_updated = Date.now()

ON CONFLICT, introduced in Postgres 9.5, is the Postgres implementation of Upsert. Here we’re evaluating if there is another row with an equal value to a constraint called email_address. If there’s a conflict we’re simply updated the last_updated field with the current timestamp.