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?
Thanks!
kj