Does PostgreSQL optimize queries in transaction?

Question

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?

Answer 1

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);