1

I am working with a little website crawler program. I use PostgresQL to store data and use such statement to update that，

INSERT INTO topic (......) VALUES (......)
ON CONFLICT DO UPDATE /* updagte all fields here */

The question is if all fields before upate and after update are really equals, would PostgresQL really update that?

Peng Qu
  • 379
  • 3
  • 12
  • Possible duplicate of [Postgres: updating not-changed rows](http://stackoverflow.com/questions/6985279/postgres-updating-not-changed-rows) – Peng Qu Nov 26 '16 at 14:55
  • flaged as duplicate to http://stackoverflow.com/questions/6985279/postgres-updating-not-changed-rows. I got my answer, thanks. – Peng Qu Nov 26 '16 at 14:56

1 Answers1

4

Postgres (like nearly all other DBMS) will not check if the target values are different then the original ones. So the answer is: yes, it will update the row even if the values are different.

However, you can easily prevent the "empty" update in this case by including a where clause:

INSERT INTO topic (......) 
VALUES (......)
ON CONFLICT (...) 
DO UPDATE 
    set ... -- update all column
WHERE topic IS DISTINCT FROM excluded;

The where clause will prevent updating a row that is identical to the one that is being inserted. To make that work correctly your insert has to list all columns of the target tables. Otherwise the topic is distinct from excluded condition will always be true because the excluded row has fewer columns then the topic row and thus it id "distinct" from it.

Adding a check for modified values has been discussed multiple times on the mailing list and has always be discarded. The main reason being, that it doesn't make sense to have the overhead of checking for changes for every statement just to cope with a few badly written ones.