postgres sql merge - ghdrako/doc_snipets GitHub Wiki
upsert: https://github.com/ghdrako/doc_snipets/wiki/postgres-upsert
- https://www.postgresql.org/docs/current/sql-merge
- https://www.depesz.com/2022/03/31/waiting-for-postgresql-15-add-support-for-merge-sql-command/
Starting from PostgreSQL 15 preferable to use the MERGE statement as it is present in SQL 2003 ANSI.
- SQL MERGE doesn't technically require a UNIQUE INDEX in contrast to INSERT ..ON CONFLICT
- is not atomic operation
select * from categories;
pk | title | description
----+-----------------------+---------------------------
1 | Database | Database related discussions
2 | Unix | Unix and Linux discussions
3 | Programming Languages | All about programming languages
4 | Machine Learning | Machine Learning discussions
5 | Software engineering | Software engineering discussions
select * from new_data;
pk | title | description
----+-----------------------+----------------------------
1 | Database Discussions | Database discussions
2 | Unix/Linux discussion | Unix and Linux discussions
Now the goal we want to achieve is to merge the two datasets as shown below:
1 | Database Discussions | Database discussions
2 | Unix/Linux discussion | Unix and Linux discussions
3 | Programming Languages | All about programming languages
4 | Machine Learning | Machine Learning discussions
5 | Software engineering | Software engineering discussions
merge into categories c
using new_data n on c.pk=n.pk
when matched then
update set title=n.title,description=n.description
when not matched then
insert (pk,title,description)
OVERRIDING SYSTEM VALUE values (n.pk,n.title,n.description);
The query above checks if there is a match between the value of the field PK of the new_data table and the value of the field of the categories table. If there is a match, the UPDATE will be executed; otherwise, the INSERT will be executed. The OVERRIDING SYSTEM VALUE clause is used because, in the INSERT statement, we have also specified the insertion of the values of the PK field taken from the new_data table, and since the PK field in the categories table is defined as GENERATED ALWAYS, without the OVERRIDING SYSTEM VALUE clause, PostgreSQL will generate an error.