In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, amount of records. And there is a question, will DB engine optimize it for me that rows won't be updated twice, or should I do it by myself?
There are a couple ways to do this without too much trouble but PostgreSQL will not leave rows intact when old = new. So you will need to do it yourself. The simplest way to do this would be to enter the new data set into a temp table, perhaps manipulate it in your transaction and then upsert the rows using a writable CTE (if on Pg 9.1 or higher). Something like:
DELETE FROM temp_foo WHERE id IN (select foo.id from foo join temp_foo t on foo.id = t.id where foo = temp_foo); WITH upsert as ( UPDATE foo SET bar = t.bar, baz = t.baz FROM temp_foo t WHERE foo.id = t.id RETURNING foo.* ) INSERT INTO foo (id, bar, baz) SELECT id, bar, baz FROM temp_foo WHERE id NOT IN (select * from upsert);