[Postgres] On conflict Do Something Clause in Postgres

Instead of first checking to see if a record already exists within your table, we can do a on conflict do update. In this command, we can ether insert a row into our table, if it does exist, then check to see if all of the columns match up. This saves us a database call and is pretty straightforward to understand.

postgres=# insert into users (user_handle, first_name, last_name, email) 
values (uuid_generate_v4(), 'Lucie', 'Jones', 'Lucie-Jones@gmail.com') on conflict do nothing:

 

We can also choose to update instead of doing nothing:

postgres=# insert into users values (uuid_generate_v4(), 'Lucie', 'Hawkins', 'Lucie-Jones@gmail.com') 
on conflict (email) do update set first_name = excluded.first_name, last_name = excluded.last_name; 

With this command (on conflict <column name> do), you choose the column in which there is a conflict (user has same email address, but has changed their last name, in this case), and then define the columns you wants to update when this conflict occurs. (eg. Lucie Jones' name will be updated to Lucie Hawkins because her account was identified by the email address conflict)

 

The excluded. refers to incoming data for that column.

 

This action is commonly referred to as an "upsert".

 

We can also update this query with a where clause.

postgres=# insert into users as u values (uuid_generate_v4(), 'Lucie', 'Cook', 'Lucie-Jones@gmail.com') 
on conflict (email) do update set first_name = excluded.first_name, last_name = excluded.last_name 
where u.first_name <> 'Lucie'; 

<> for 'does not equal'

In this example, if there is an email conflict and the original records firstname is equal to 'Lucie' the row will not be updated