PostgreSQL - Day 7 - Part 2

• Sun, Jun 12, 2022
• 2-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

In this part, we’ll learn UPSERT and how to deal with conflict when insert.

PostgreSQL upsert

ON CONFLICT Clause

https://www.postgresql.org/docs/14/sql-insert.html

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency.

This is also known as UPSERT — “UPDATE or INSERT”.

43. On Conflict Do Nothing

We’re going to learn how to deal with duplicate key errors or exceptions.

• If you insert a record with an existing primary key, you get the error: duplicate key value violates unique constraint "person_pkey"

You can handle the error by the ON CONFLICT DO NOTHING statement.

44. Upsert

If you really need to update information on the conflict, you can use the Upsert statement. The syntax is ON CONFLICT (<column_name>) plus DO UPDATE SET <expression>.

• When duplicate id, update email and country_of_birth.

  insert into person (id, first_name, last_name, email, gender, date_of_birth, country_of_birth)
  values (7, 'Peder', 'Ibbitson', 'newEmail@gmail.com', 'Male', '2006-07-15', 'Japen')
  ON CONFLICT (id) DO UPDATE
  SET email = EXCLUDED.email, country_of_birth = EXCLUDED.country_of_birth;
  

  

  • EXCLUDED is the keyword targeted to the rows proposed for insertion.

    The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table’s name (or an alias), and to rows proposed for insertion using the special excluded table.