Thread: Is there a PostgreSQL utility that is similiar to Oracles sql loader?

We  are trying  to load our PostgreSQL DB with data that contains many
corrupted rows.  I recall that sql loader will skip corrupted rows and
keep  going.  We are using the PostgreSQL copy command to load and it
just gives up when the first corrupted row is encountered.

Thanks in a advance,
-=bill

Wm.A.Stafford wrote:
> We  are trying  to load our PostgreSQL DB with data that contains many
> corrupted rows.  I recall that sql loader will skip corrupted rows and
> keep  going.  We are using the PostgreSQL copy command to load and it
> just gives up when the first corrupted row is encountered.
>
> Thanks in a advance,
> -=bill

This might not help, but I don't know of a tool to do what you want (I
am not an experts, others here may).

How difficult would it be to write a script (perl?) to read in the dump
file and translate all of the COPY... calls into individual INSERTs with
autocommit on and just log the bad rows? Sorry if this doesn't help. :)

Madi

Le vendredi 17 novembre 2006 22:07, Wm.A.Stafford a écrit :
> We  are trying  to load our PostgreSQL DB with data that contains many
> corrupted rows.  I recall that sql loader will skip corrupted rows and
> keep  going.  We are using the PostgreSQL copy command to load and it
> just gives up when the first corrupted row is encountered.

pgloader is the tool you're looking for, it will give you reject log file and
reject data file, and has many useful options, as for example:
  -p, --pedantic        pedantic mode, stop processing on warning
  -n, --dry-run         simulate operations, don't connect to the db
  -T, --truncate        truncate tables before importing data
                        number of input lines to process
  -F FROMCOUNT, --from=FROMCOUNT
                        number of input lines to skip
  -I FROMID, --from-id=FROMID
                        wait for given id on input to begin

Pedantic mode makes pgloader behave as COPY command, damn usefull in debug
mode or if you have the opportunity to correct data.

As I just gave you information to download code and debian package, I won't
insist that much on it :)
--
Dimitri Fontaine
http://www.dalibo.com/

Wm.A.Stafford wrote:
> We  are trying  to load our PostgreSQL DB with data that contains many
> corrupted rows.  I recall that sql loader will skip corrupted rows and
> keep  going.  We are using the PostgreSQL copy command to load and it
> just gives up when the first corrupted row is encountered.
>
> Thanks in a advance,
> -=bill
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
You could give Lightning Admin a try, we have full import capabilities
(though not as fast as using COPY from a CSV file)

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration
Only $9.99 until December 1st 2006