I have a table with unique identifier sid
, somecolumn
and boolean changed
that is needed by other program to detect change in row, I'm using insert into ... on conflict (sid) DO UPDATE ...
to insert data that's not already in there, but I also want to update somecolumn
and set changed
to true, only if somecolumn
doesn't match excluded one.
to make it bit clearer
- if
sid
doesn't exist, insert data -
if
sid
exists andsomecolumn
matches excluded `somecolumn, do nothing -
if
sid
exists andsomecolumn
doesn't match, updatesomecolumn
with new value
and setchanged
to true
is there clean way to do this? I'd prefer to do this without making multiple queries, I'm inserting hundreds of values in each query.
Best Answer
You can add a WHERE clause to the UPDATE part of an INSERT ... ON CONFLICT
If you need to deal with NULL values in
somecolumn
use