Will postgres really update the page file when all fields are equal before and after the update?

Question

I am working with a small website crawler program. I use PostgresQL to store data and use such a statement to update,

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

The question is, are all the fields before upate and after update really equal, will PostgresQL really update this?

Answer 1

Postgres (like almost all other DBMSs) will not check if the target values ​​differ from the original ones. Therefore, the answer: yes, it will update the line, even if the values ​​are different.

However, you can easily prevent an “empty” update in this case by including the 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 a row that matches the one inserted from being updated. To make this work correctly, there is in your tab to display all columns of the target tables. Otherwise, the topic is distinct from excluded condition will always be true, since the excluded row has fewer columns and then the topic row and therefore it will be different from it.

---

Adding validation for changed values ​​has been discussed several times on the mailing list and is always discarded. The main reason is that it makes no sense to have the overhead of checking for changes for each statement to deal with a few badly written ones.