Thread: insert continue on error

Hello everybody.

I'm trying to migrate our data mine from a MS Access backend to a
postgresql backend, and I'm bumping into problems with the conversion.
I've got the basic db and tables set up. I want to transfer data from
one table to another with insert into ...  select from. The from table
and into table both have the same field names, but the types for the
first are all text, and the second are specific (boolean, numeric(10,2),
etc), with cast and custom conversion functions doing the translation.
However every once in a while a record will contain data that I didn't
forsee/wasn't cast properly and the record will bounce. This of course
aborts the entire process.

What I'd like to know is if there's a way to have postgresql still
insert the other rows, and either bounce the bad row to another table,
or log the error in another table/file. I'm sure there's a way to do
this, but I'm still pretty new to postgresql.

TIA for any assistance you can give me.

On 16/10/2007 20:37, Tom Hart wrote:

> What I'd like to know is if there's a way to have postgresql still
> insert the other rows, and either bounce the bad row to another table,
> or log the error in another table/file. I'm sure there's a way to do
> this, but I'm still pretty new to postgresql.

If you do the insertions within a pl/pgsql function, you can use an
EXCEPTION block to handle the error whatever way you like - have a look
here:

http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

There are a couple of useful examples.

HTH,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Tue, Oct 16, 2007 at 03:37:46PM -0400, Tom Hart wrote:
> I'm trying to migrate our data mine from a MS Access backend to a
> postgresql backend, and I'm bumping into problems with the conversion.

When I've had to do this before, I've tended to create a table with the
same columns but all of text type.  I can then import into there without
worry too much about errors.  I'd then write a script to do the movement
across into the final tables.  If the access database is still live then
you can spend a while getting the script right, then at some appropriate
time you can just quickly move everything over.


  Sam