Thread: Pg_Restore with --clean option
I want to restore data of a single table. Before restoring the data I disabled all the triggers and constraints on that table. I used the restore command with --clean option so that data gets deleted from that table and then fresh data get inserted. But this is throwing error: duplicate key value violates unique constraint. How can I restore the data to a table that already has data? Thanks, -- View this message in context: http://www.nabble.com/Pg_Restore-with---clean-option-tp25693180p25693180.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Wed, 2009-09-30 at 22:14 -0700, Jyoti Seth wrote: > I want to restore data of a single table. Before restoring the data I > disabled all the triggers and constraints on that table. I used the restore > command with --clean option so that data gets deleted from that table and > then fresh data get inserted. --clean drops the tables, not the data. > But this is throwing error: duplicate key value violates unique constraint. > > How can I restore the data to a table that already has data? Well, you could just not use --clean, which would insert the data into existing tables. But if you have conflicting data in your tables, you need a more elaborate scheme, depending on how exactly you plan to resolve those conflicts. I think you might be a bit confused about the proper workflow with pg_dump and pg_restore and therefore are not giving us accurate information. Perhaps some more detail would help.
On Wed, Sep 30, 2009 at 11:14 PM, Jyoti Seth <jyotiseth2001@gmail.com> wrote: > > I want to restore data of a single table. Before restoring the data I > disabled all the triggers and constraints on that table. I used the restore > command with --clean option so that data gets deleted from that table and > then fresh data get inserted. You could run truncate on the table then just restore its data from the other machine. If you still get dups then there's dups in the original data.