On Fri, 20 Feb 2004, Sally Sally wrote:
> Thank you all for replying. I appreciate the tips. Apologies to those who
> were offended by the html formating.
> Scott, quick question. The reason I assumed insert would be "safer" than
> copy is because the docs say that in the case of copy it fails on a single
> corrupted row whereas insert won't?
right, but that's not exactly "safer" from a data integrity standpoint.
After all, if you insert 100M rows, and one fails, you now have 99.99999%
of your data in your database. If you don't check the import for errors,
you now have an incomplete load, and may not know it.
Wrapping the inserts inside of a begin;commit; pair is a neat trick to
both speed things up (postgresql is MUCH faster inserting 100M rows in one
big transaction than individually) AND it makes sure you don't get a
partial load.
Partial loads are a SERIOUS issue when you're importing 10k rows a day
into a database with millions of rows. You usually want all or nothing.
And don't worry about me being offended by html formatting, my email
client, pine, automatically strips that stuff out of your emails. Of
course, others, with their primitive email clients (haha, like there's
anything more primitive than pine :-) will appreciate the change.
What's funny is that when they reply to your email, then I see the html in
the stuff they quoted, but not in your original email.
