Using UPSERT

In this section, we will look at the PostgreSQL way to make an UPSERT statement. There is no UPSERT statement in SQL, but the same effect can be achieved using an INSERT SQL statement.

UPSERT – the PostgreSQL way

In PostgreSQL, the UPSERT statement does not exist as in other DBMSes. An UPSERT statement is used when we want to insert a new record on top of the existing record or update an existing record. To do this in PostgreSQL, we can use the ON CONFLICT keyword:

INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;

Here, ON CONFLICT means that the target action is executed when the record already exists (meaning when a record with the same primary key exists). The target action could be this:

 DO NOTHING

Alternatively, it could be the following:

DO UPDATE SET { column_name = { expression | DEFAULT } |
     ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
     ( column_name [, ...