Thread: when COPY violates Primary Keys

Hello all,

I have a ascii dump file based on the COPY operation.

lets say I restore this dump into a live database with applications
doing INSERTs and UPDATEs onto it.

in case the COPY of a register causes a primary key (or UNIQUE, or FK)
violation does the psql restore command try to continue the operation
until it has parsed the whole dump, or does it abort on the first
violation ?

thx
Joao

Joao Ferreira gmail wrote:
> Hello all,
>
> I have a ascii dump file based on the COPY operation.
>
> lets say I restore this dump into a live database with applications
> doing INSERTs and UPDATEs onto it.
>
> in case the COPY of a register causes a primary key (or UNIQUE, or FK)
> violation does the psql restore command try to continue the operation
> until it has parsed the whole dump, or does it abort on the first
> violation ?

Copy is all or nothing. It'll abort as soon as a violation happens.

the bulkloader on pgfoundry (http://pgfoundry.org/projects/pgbulkload/)
can apparently handle this for you...

--
Postgresql & php tutorials
http://www.designmagick.com/

Chris <dmagick@gmail.com> wrote:

> Joao Ferreira gmail wrote:
> > in case the COPY of a register causes a primary key (or UNIQUE, or FK)
> > violation does the psql restore command try to continue the operation
> > until it has parsed the whole dump, or does it abort on the first
> > violation ?
>
> Copy is all or nothing. It'll abort as soon as a violation happens.
>
> the bulkloader on pgfoundry (http://pgfoundry.org/projects/pgbulkload/)
> can apparently handle this for you...

No, pg_bulkload is all or nothing for constraints violations.
Please use pgloader (http://pgfoundry.org/projects/pgloader/)
for the purpose.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center