Using UPSERT

In this section, we will look at some interesting features of the insert statement:

• How to make an upsert statement starting from an insert statement
• How to make a SQL query that inserts the same data and returns the record inserted

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...