PostgreSQL UPSERT

UPSERT (update or insert):  When you insert a row into the table , Postgre will update the row if data already exist else it will create new row.

NOTE :  In order to perform UPSERT operation you must have UNIQUE constraints.

For example name column is UNIQUE

Create table example:

CREATE TABLE customer(
id serial primary key,
name varchar(20) unique not null,
date timestamp default NOW()
)

In customer table id the customer name saheb already exist then do nothing

INSERT INTO customer (name)
VALUES ('saheb')
ON CONFLICT
DO NOTHING;

Data table

In the above table I want to update the date if the customer name is saheb , else it will create a new row with the name saheb and the current date

INSERT INTO customer (name)
VALUES ('saheb')
ON CONFLICT (name)
DO UPDATE SET 
name =EXCLUDED.name
date = NOW();

EXECLUDED.name means exclude the value which I have provided in the VALUES()

INSERT INTO customer (name)
VALUES ('bubai')
ON CONFLICT (name)
DO UPDATE SET 
name =EXCLUDED.name
date = NOW();

SELECT * FROM customer;

OUTPUT - 

Data table

Since name bubai name is not there in the table so it has created a new row.