Re: psql script error handling

On Fri, Dec 29, 2006 at 07:21:12PM -0500, James Neff wrote:
> I have an sql script that I am trying to execute in psql client on the
> database itself. The script is just a bunch (hundreds of thousands) of
> INSERT statements.

Hundreds of thousands? Is there a reason you're not using COPY
instead of INSERT? COPY would be more efficient.

> I don't know how, but I seem to have bad characters throughout my file
> and when I run the script it will of course error out complaining about
> the invalid character.

What's the exact error? Is it something like 'invalid byte sequence
for encoding "UTF8"'? If so then try setting client_encoding to
whatever encoding the data is in, such as latin1 or win1252 (the
latter is likely if the data originated on Windows). Or use a
program like iconv or uconv to convert the data to the server's
encoding.

> I figure out how many insert statements were successful and use sed
> to chop of those statements from the sql script file. I then use VI
> and delete out the bad character and re-run the script.

Are you doing the inserts in a transaction? If so then none if the
inserts in the failed transaction would have committed so they'd
need to be done again unless you're wrapping the failures in a
savepoint by setting ON_ERROR_ROLLBACK. If you're not using a
transaction then those inserts are going to be slow because each
one is its own transaction, necessitating a disk write.

> There's got to be a better way to do this. Is there a way in a psql
> script to try to execute the INSERT statement and if theres a problem to
> dump it to a log file and go on with the others?

pgloader can do that but I'd suggest identifying and fixing the
problem rather than trying to work around it. You might just need
to set client_encoding or convert the data to the server's encoding.

--
Michael Fuhr