On Apr 18, 2006, at 4:03 PM, Kynn Jones wrote:
I have a Perl script that is supposed to make a large number of
inserts in a PostgreSQL database. Performing individual inserts
with SQL's INSERT command is too slow, however, I can use a
"COPY ... from stdin" approach that is fast enough. Basically, I
fork a psql process (yes, I'm on unix) and have the parent write
the new records into the child, as part of a COPY from stdin
command. Admittedly, this is very convoluted, but it works.
Well, it almost works. It did in my preliminary tests, but failed
in the real run with the actual database because the owner of the
tables that get modified by this COPY statement is a user for which
the psql command requires a password ( i.e. this user exists solely
for the purpose of owning the database in question; it does not
have a UID, nor disk space assigned to it), and I don't know how to
give this password programmatically.
So I'm back at the drawing board. How can I make fast bulk inserts
into a PostgreSQL database from within a Perl script?
Assuming you're using DBI, perldoc DBD::Pg will be enlightening.
Check the pg_putline and pg_endcopy functions.
Cheers,
Steve
