Upsert in PostgreSQL Using ON CONFLICT
If you need update a row in PostgreSQL if it already exists OR create a new row if it doesn't already exist, using the ON CONFLICT command will do it
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.
INSERT INTO users
VALUES ('marcus','adams', 'm.adams@email.com')
ON CONFLICT (email_address)
DO UPDATE SET last_updated = Date.now()The breakdown
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.
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.