Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B679D632680 for ; Thu, 1 Oct 2009 03:06:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 92746-01 for ; Thu, 1 Oct 2009 06:06:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by mail.postgresql.org (Postfix) with SMTP id A07B3633F60 for ; Thu, 1 Oct 2009 03:06:04 -0300 (ADT) Received: (qmail invoked by alias); 01 Oct 2009 06:06:02 -0000 Received: from fsgw.f-secure.com (EHLO [10.128.134.40]) [193.110.108.33] by mail.gmx.net (mp037) with SMTP; 01 Oct 2009 08:06:02 +0200 X-Authenticated: #495269 X-Provags-ID: V01U2FsdGVkX19oGPNVsYxoOIC6O4MqeQ+TRwEhSq09cuHF3JfI/a ZXn3k5wdXEvH+H Subject: Re: Pg_Restore with --clean option From: Peter Eisentraut To: Jyoti Seth Cc: pgsql-sql@postgresql.org In-Reply-To: <25693180.post@talk.nabble.com> References: <25693180.post@talk.nabble.com> Content-Type: text/plain Date: Thu, 01 Oct 2009 09:05:53 +0300 Message-Id: <1254377153.30588.2.camel@fsopti579.F-Secure.com> Mime-Version: 1.0 X-Mailer: Evolution 2.26.3 Content-Transfer-Encoding: 7bit X-Y-GMX-Trusted: 0 X-FuHaFi: 0.72 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.567 tagged_above=-10 required=5 tests=AWL=0.032, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200910/2 X-Sequence-Number: 33201 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.