Re: How to implement the skip errors for copy from ?

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: xbzhang <xbzhang(at)kingbase(dot)com(dot)cn>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: How to implement the skip errors for copy from ?
Date: 2014-06-16 18:37:41
Message-ID: 20140616183741.GA18688@eldon.alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Pavel Stehule wrote:
> 2014-06-16 11:46 GMT+02:00 xbzhang <xbzhang(at)kingbase(dot)com(dot)cn>:
>
> >
> > I want to implement the skip errors for copy from,lik as :
> > create table A (c int primary key);
> > copy A from stdin;
> > 1
> > 1
> > 2
> > \.
> >
> > copy will failed:
> > ERROR: duplicate key violates primary key constraint "CC_PKEY"
> > CONTEXT: COPY CC, line 2: "1"
> >
> > I want skip the error, and continue to copy the reset of tuple. The result
> > will be that there are two rows in table A: 1 and 2.
> >
> > how to implement that ? Anybody give me some suggestion?
>
> you should to reimplement a copy procedure to use a subtransactions. Using
> subtransaction for any row is too expensive, but you can do subtransaction
> per 1000 rows, and when some exception is raised, then store data per one
> row/one subtransaction.

See http://pgloader.io/ for a ready-made solution.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2014-06-16 19:13:43 Re: IMPORT FOREIGN SCHEMA statement
Previous Message Shreesha 2014-06-16 18:04:07 Re: How to change the pgsql source code and build it??