October 25, 2023
Summary: in this tutorial, you will learn how to merge data from files to PostgreSQL tables using DBeaver.
Sometimes there are situations when you want to ignore the current primary key value when importing into a table. In addition to the “INSERT INTO” syntax, PostgreSQL supports merge insert syntax, in the form of “ON CONFLICT DO NOTHING” and “ON CONFLICT DO UPDATE SET”.
Insert examples:
“ON CONFLICT DO NOTHING”
INSERT INTO language_insert(language_id, name, last_update)
VALUES(1, 'English', '2006-02-15 05:02:19.0') ON CONFLICT DO NOTHING;
“ON CONFLICT DO UPDATE SET”
INSERT INTO language_insert(language_id, name, last_update)
VALUES(1, 'English', '2006-02-15 05:02:19.0')
ON CONFLICT (language_id)
DO UPDATE SET (language_id, name, last_update) = (EXCLUDED.language_id, EXCLUDED.name, EXCLUDED.last_update);
The choice of the replacement method is in the import settings - in “Data load settings”.
By default, the selection is <None>, you can select other options from the drop-down list. The options available includes the ignore method “ON CONFLICT DO NOTHING” and the replace method “ON CONFLICT DO UPDATE SET”.
Let’s take a look at an example of how this works. We use a small, simple, slightly-modified Sakila table - sakila.language.
CREATE TABLE language_insert (
language_id smallint NOT NULL,
name char(20) NOT NULL,
last_update timestamp NOT NULL,
PRIMARY KEY (language_id)
);
INSERT INTO sakila.language_insert (language_id, name, last_update) VALUES
(1,'English','2006-02-15 05:02:19.0'),
(2,'Italian','2006-02-15 05:02:19.0'),
(3,'Japanese','2006-02-15 05:02:19.0');
If we try to execute this request twice, we will get the following error:
ERROR: duplicate key value violates unique constraint "language_insert_language_id_pkey"
DETAIL: Key (language_id)=(1) already exists.
Let’s take a new .csv file with the following content and try to use the replace methods.
"language_id","name","last_update"
1,Spanish,"2020-04-20 05:02:19.0"
2,Russian,"2020-05-20 05:02:19.0"
3,Belgian,"2020-06-20 05:02:19.0"
4,Mandarin,"2006-02-15 05:02:19.0"
5,French,"2006-02-15 05:02:19.0"
6,German,"2006-02-15 05:02:19.0"
If we set the ignore method “ON CONFLICT DO NOTHING” in the settings, the result of the insert will look like this:
There will be no insertion errors, the first three lines will not change, and the fourth to sixth lines will be added to the table.
If we set the “REPLACE INTO” method in the settings, the result of the insert will look like this:
There will be no insertion errors, the first three lines will be replaced and the fourth to sixth lines will be added to the table.