PostgreSQL - insert or update row

In this article, we will take a closer look at the data insertion process. Including how to avoid errors when inserting duplicate data. We will perform all operations on the PostgreSQL test database, you can download a dump of this database at the link: https://www.postgresqltutorial.com/postgresql-sample-database/

Какой самый простой способ вставить данные в таблицу customer? Напомню, что синтаксис будет следующий:

INSERT INTO customer (active, activebool, address_id, create_date, email, first_name, last_name, last_update, store_id) VALUES (1, default, 10, default, 'test2@test.com', 'Andrey', 'Vasilyev', default, 2)

To insert multiple values, the syntax is as follows:

INSERT INTO customer (active, activebool, address_id, create_date, email, first_name, last_name, last_update, store_id) VALUES (1, default, 10, default, 'test2@test.com', 'Andrey', 'Vasilyev', default, 2), (1, default, 11, default, 'test4@test.com', 'Ivan', 'Ivanov', default, 2) returning customer_id;

Notice the phrase "returning customer_id" at the end? It allows, after insertion, to receive the id of the added records in the response.

Now let's do the following very common thing - make the email field in this table unique:

ALTER TABLE customer ADD CONSTRAINT email_unique UNIQUE (email);

And now let's try to insert an already existing record like this:

INSERT INTO customer (active, activebool, address_id, create_date, email, first_name, last_name, last_update, store_id) SELECT active, activebool, address_id, create_date, email, first_name, last_name, last_update, store_id FROM customer WHERE customer_id = 10 returning customer_id

Those with this request, we are trying to insert customer_id with number 10. And when this request is executed, we will receive an error:

ERROR:  duplicate key value violates unique constraint "email_unique"

So, now, in order to add a new user, we need to execute the following query before the insert statement:

SELECT count(1) FROM customer WHERE email = 'test2@test.com'

Those check that the user with this email is not in the database.

However, there is a much easier way to do this. Let's try the following query:

INSERT INTO customer (active, activebool, address_id, create_date, email, first_name, last_name, last_update, store_id) VALUES (1, default, 19, default, 'helen.harris@sakilacustomer.org', 'Helen', 'Harris', default, 1) on conflict do nothing

And note that we will not receive any error in response, the request will be completed successfully, despite the fact that the user is present in the database.

This approach has another advantage. Instead of just doing nothing when a duplicate is found, we can update the record. For example, let's imagine the situation that the user Helen has changed his place of residence, i.e. moved to another city. And when we try to re-register, we need to update the address, as well as the first and last name if they have also been changed. The following query will help us do this:

INSERT INTO customer (active, activebool, address_id, create_date, email, first_name, last_name, last_update, store_id) VALUES (1, default, 20, default, 'helen.harris@sakilacustomer.org', 'Helena', 'Harry', default, 1) on conflict (email) do update set first_name = excluded.first_name, last_name = excluded.last_name, address_id = excluded.address_id returning customer_id

Note that in this request we have specified the email field, by which we check for duplicates, indicate the action that we must perform (do update). And then we specify the mapping by which we update the fields (first_name = excluded.first_name, last_name = excluded.last_name, address_id = excluded.address_id). By the excluded keyword, we refer to our new inserted value, thereby updating the field values. And with the phrase "returning customer_id" we get the customer id in response.