Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
1  
There is no such optimization (to my knowledge there is no RDBMS that can do this). So you'll have to roll your own solution. – dezso Feb 21 at 12:38

1 Answer

up vote 2 down vote

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.