1

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 and somecolumn matches excluded `somecolumn, do nothing

  • if sid exists and somecolumn doesn't match, update somecolumn with new value and set changed 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.

1 Answer 1

1

You can add a WHERE clause to the UPDATE part of an INSERT ... ON CONFLICT

insert into the_table (sid, somecolumn)
values (...)
on conflict (sid) do update 
  set somecolumn = excluded.somecolumn, 
      changed = true
where the_table.somecolumn <> excluded.somecolumn;

If you need to deal with NULL values in somecolumn use

where the_table.somecolumn is distinct from excluded.somecolumn

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.