Обсуждение: 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