Menu
Is it possible to express an upsert query, where nothing happens if the inserted data doesn't have any changes compared to what is already in the database?
Currently I have:
insert into feeds (link, title, category)
values (...)
on conflict (link)
do update set (title, category, _updated)
= (..., now()::timestamp)
468
In relational databases, the term upsert is referred to as merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).
Postgres now supports UPSERT - git.postgresql.org/gitweb/…
Firstly we have to mention the table name followed by column names (attributes) where we want to insert rows. Secondly, we must enter the values, separated by a comma after the VALUE clause. Finally, every value must be in the same order as the sequence of attribute lists is provided while creating a particular table.
ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.
You can add a where clause to the update part:
insert into feeds (link, title, category)
values (...)
on conflict (link)
do update
set (title, category, _updated) = (..., now()::timestamp)
where (feeds.title, feeds.category) is distinct from (excluded.title, excluded.category)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
