CODEWITHSUNDEEP
postgresql
Peng Qu
Peng Qu

Reputation: 389

would postgres really update page file when fields are all equals before and after update?

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?

Upvotes: 1

Views: 222

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 4

Related Questions