Обсуждение: 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.