Re: Bulk inserts within a Perl script? - Mailing list pgsql-general

From Steve Atkins
Subject Re: Bulk inserts within a Perl script?
Date
Msg-id E244C93F-9224-4C9D-8D4D-6884140A69DA@blighty.com
Whole thread Raw
In response to Bulk inserts within a Perl script?  ("Kynn Jones" <kynnjo@gmail.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: "Kynn Jones"
Date:
Subject: Bulk inserts within a Perl script?
Next
From: Tom Lane
Date:
Subject: Re: Bulk inserts within a Perl script?