X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 452A952835 for ; Thu, 23 Jun 2005 17:17:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34794-10 for ; Thu, 23 Jun 2005 20:17:20 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 7A93D5280D for ; Thu, 23 Jun 2005 17:17:19 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j5NKHGat078639 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 23 Jun 2005 14:17:19 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j5NKHGA5038553; Thu, 23 Jun 2005 14:17:16 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j5NKHGt0038552; Thu, 23 Jun 2005 14:17:16 -0600 (MDT) (envelope-from mfuhr) Date: Thu, 23 Jun 2005 14:17:16 -0600 From: Michael Fuhr To: David Bear Cc: pgsql-admin@postgresql.org Subject: Re: psql copy errors Message-ID: <20050623201715.GA38419@winnie.fuhr.org> References: <20050623192744.GC398@asu.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050623192744.GC398@asu.edu> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/178 X-Sequence-Number: 17933 On Thu, Jun 23, 2005 at 12:27:44PM -0700, David Bear wrote: > > I'm finding the \copy is very brittle. It seems to stop for everyone > little reason. Is there a way to tell it to be more forgiving -- for > example, to ignore extra data fields that might exists on a line? > > Or, to have it just skip that offending record but continue on to the > next. Calling this behavior "brittle" is debatable; some people would call it "reliable": either the whole input validates -- matches what's expected -- or the command rejects it. This might sound unreasonable if a partial load is acceptable, but it's desirable behavior if a partial load is unacceptable. Some would argue that the correct solution is to fix the data, not to make the command that loads the data more forgiving. The developers' TODO list has a few outstanding items to enhance COPY, but I don't know if anybody's working on them: http://www.postgresql.org/docs/faqs.TODO.html * Allow COPY to report error lines and continue This requires the use of a savepoint before each COPY line is processed, with ROLLBACK on COPY failure. * Have COPY return the number of rows loaded/unloaded (?) -- Michael Fuhr http://www.fuhr.org/~mfuhr/