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?
1 Answer
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);