Re: Bulk inserts within a Perl script?

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