Re: Load a csv file into a pgsql table

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/19/06 13:26, Steve Atkins wrote:
>
> On Sep 19, 2006, at 11:15 AM, Emi Lu wrote:
>
>> Greetings,
>>
>>
>> *Except* copy command, are there other quick ways to load data from a
>> csv file into a pgsql table please?
>
> There are several bulk loaders, but I believe they all use COPY behind
> the scenes.
>
> If copy isn't an option then your best bet will be many inserts in a
> transaction, but that'll be significantly slower. You could cobble
> together a loader using perl, DBI and one of the CPAN CSV modules fairly
> easily.
>
> (But I can't think of any reason why you wouldn't use copy, so you must
> have some constraint you haven't mentioned - can you expand on why copy
> isn't an option?)

COPY has great facilities for specifying the physical layout of the
CSV file, but is otherwise limited. Facilities that I miss are:

statistics: loaded 10000 rows, loaded 20000 rows, ... etc.

skip: if the COPY dies (or is killed after 10Mn rows have been
loaded, it's so useful to be able to add "--skip=10425000"
to the command and have the bulk loaded quickly scan to
that record. Yes, tail(1) can slice off the unloaded
records, but that means that now you have 2 files. Messy.

transactions: goes hand-in-glove with statistics and skip.

exceptions file: if you have a unique index on the table, and
one of the input records is a duplicate, kick
it out to an exceptions file, note it to stderr
and keep on loading.

A "fields" option would also be handy. This is for when the number
of fields in the input file does not equal those in the table.

Just MHO, of course.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFEFPsS9HxQb37XmcRAkxpAJ9czWEjP+lYDInS8dVeN9OLYY865wCfU0Fm
/Z3FxL6o5XCU3SivPFQDVEc=
=K438
-----END PGP SIGNATURE-----