pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql copy errors
6+ messages / 6 participants
[nested] [flat]
* psql copy errors
@ 2005-06-23 19:27 David Bear <David.Bear@asu.edu>
2005-06-23 20:17 ` Re: psql copy errors Michael Fuhr <mike@fuhr.org>
2005-06-23 21:48 ` Re: psql copy errors Vladimir Yevdokimov <vladimir@givex.com>
0 siblings, 2 replies; 6+ messages in thread
From: David Bear @ 2005-06-23 19:27 UTC (permalink / raw)
To: pgsql-admin
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.
I've got a tab delimited file, but if \copy sees any extra tabs in the
file it just stops at that record. I want to be able to control what
pg does when it hits an exception.
I'm curious what others do for bulk data migration. Since copy seems
so brittle, there must be a better way...
--
David Bear
phone: 480-965-8257
fax: 480-965-9189
College of Public Programs/ASU
Wilson Hall 232
Tempe, AZ 85287-0803
"Beware the IP portfolio, everyone will be suspect of trespassing"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql copy errors
2005-06-23 19:27 psql copy errors David Bear <David.Bear@asu.edu>
@ 2005-06-23 20:17 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-06-23 20:17 UTC (permalink / raw)
To: David Bear <David.Bear@asu.edu>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql copy errors
2005-06-23 19:27 psql copy errors David Bear <David.Bear@asu.edu>
@ 2005-06-23 21:48 ` Vladimir Yevdokimov <vladimir@givex.com>
1 sibling, 0 replies; 6+ messages in thread
From: Vladimir Yevdokimov @ 2005-06-23 21:48 UTC (permalink / raw)
To: David.Bear@asu.edu; +Cc: pgsql-admin
On June 23, 2005 03:27 pm, 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.
>
> I've got a tab delimited file, but if \copy sees any extra tabs in the
> file it just stops at that record. I want to be able to control what
> pg does when it hits an exception.
>
> I'm curious what others do for bulk data migration. Since copy seems
> so brittle, there must be a better way...
>
You may use '-d' option of pg_dump in which case it dumps data into INSERT statements.
In this case when you load the damped data it will process tabs properly and will fail any invalid records but finish the process itself.
If you redirect output into a separate file you can analyze later how many records failed.
May be that's what you need in your case.
The only problem with this method I know is that it takes longer to load the data as it does full validation for each record.
--
Vladimir Yevdokimov <vladimir@givex.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql copy errors
@ 2005-06-24 05:52 李江华 <ljh1469@alibaba-inc.com>
2005-06-24 14:43 ` Re: psql copy errors Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 6+ messages in thread
From: 李江华 @ 2005-06-24 05:52 UTC (permalink / raw)
To: David.Bear@asu.edu <David.Bear@asu.edu>; +Cc: pgsql-admin
David Bear:
Yes. I agree with you.
\copy is really too brittle.
I wonder why \copy is not like oracle's sqlldr?
I think sqlldr is more powerful. When using sqlldr,we can specify the maximum error records we allow,and we can also specify the number we should commit in every transaction.
I think PostgreSQL should consider this.
Another aspect is also important. Oracle has better partition table facilities,it's especially suitable for large tables,as well as index partition concepts.But PostgreSQL has no such concepts.These are really important for large database.
I think if PostgreSQL can implement those above in the future.It will be more powerful and more suitable for large business application.
======= 2005-06-24 03:27:44 you 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.
>
>I've got a tab delimited file, but if \copy sees any extra tabs in the
>file it just stops at that record. I want to be able to control what
>pg does when it hits an exception.
>
>I'm curious what others do for bulk data migration. Since copy seems
>so brittle, there must be a better way...
>
>--
>David Bear
>phone: 480-965-8257
>fax: 480-965-9189
>College of Public Programs/ASU
>Wilson Hall 232
>Tempe, AZ 85287-0803
> "Beware the IP portfolio, everyone will be suspect of trespassing"
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
= = = = = = = = = = = = = = = = = = = =
Best regards!
李江华
Seamus Dean
Alibaba.com
TEL:0571-85022088-2287
ljh1469@alibaba-inc.com
2005-06-24
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql copy errors
2005-06-24 05:52 Re: psql copy errors 李江华 <ljh1469@alibaba-inc.com>
@ 2005-06-24 14:43 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-06-24 14:56 ` Re: psql copy errors Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2005-06-24 14:43 UTC (permalink / raw)
To: ljh1469@alibaba-inc.com; +Cc: David.Bear@asu.edu <David.Bear@asu.edu>; pgsql-admin
On Fri, 2005-06-24 at 00:52, 李江华 wrote:
> David Bear:
>
> Yes. I agree with you.
> \copy is really too brittle.
> I wonder why \copy is not like oracle's sqlldr?
> I think sqlldr is more powerful. When using sqlldr,we can specify the maximum error records we allow,and we can also specify the number we should commit in every transaction.
While I wouldn't mind have the versatility of sqlldr, I would HATE to
have to deal with such a clunky interface. The only features of sqlldr
I actually like are the ones that allow rejected records to go into a
separate file.
But like most of oracle's tools, it's awkward to use, and requires a lot
of up front work even if you only want a simple load. Sure, add some
features to \copy, but don't emulate the WAY those features are
implemented oracle.
> Another aspect is also important. Oracle has better partition table facilities,it's especially suitable for large tables,as well as index partition concepts.But PostgreSQL has no such concepts.These are really important for large database.
No one would complain if someone stepped up and programmed a decent
table partitioning patch. I have a feeling it's one of the things on
the TODO list that will happen in the next version or two.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql copy errors
2005-06-24 05:52 Re: psql copy errors 李江华 <ljh1469@alibaba-inc.com>
2005-06-24 14:43 ` Re: psql copy errors Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-06-24 14:56 ` Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2005-06-24 14:56 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: ljh1469@alibaba-inc.com, "David.Bear@asu.edu" <David.Bear@asu.edu>; pgsql-admin
On Fri, Jun 24, 2005 at 09:43:43AM -0500, Scott Marlowe wrote:
> On Fri, 2005-06-24 at 00:52, ????????? wrote:
> > David Bear:
> >
> > Yes. I agree with you.
> > \copy is really too brittle.
> > I wonder why \copy is not like oracle's sqlldr?
> > I think sqlldr is more powerful. When using sqlldr,we can specify the maximum error records we allow,and we can also specify the number we should commit in every transaction.
>
> While I wouldn't mind have the versatility of sqlldr, I would HATE to
> have to deal with such a clunky interface. The only features of sqlldr
> I actually like are the ones that allow rejected records to go into a
> separate file.
There's a PGLoader project in pgfoundry that may suit some people.
> > Another aspect is also important. Oracle has better partition
> > table facilities,it's especially suitable for large tables,as
> > well as index partition concepts.But PostgreSQL has no such
> > concepts.These are really important for large database.
>
> No one would complain if someone stepped up and programmed a decent
> table partitioning patch. I have a feeling it's one of the things on
> the TODO list that will happen in the next version or two.
I think Simon Riggs and crew are working on this.
--
Alvaro Herrera (<alvherre[a]surnet.cl>)
Jude: I wish humans laid eggs
Ringlord: Why would you want humans to lay eggs?
Jude: So I can eat them
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-24 14:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 19:27 psql copy errors David Bear <David.Bear@asu.edu>
2005-06-23 20:17 ` Michael Fuhr <mike@fuhr.org>
2005-06-23 21:48 ` Vladimir Yevdokimov <vladimir@givex.com>
2005-06-24 05:52 Re: psql copy errors 李江华 <ljh1469@alibaba-inc.com>
2005-06-24 14:43 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-06-24 14:56 ` Alvaro Herrera <alvherre@surnet.cl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox