Re: type-casting failures - Mailing list pgsql-general

On Jun 21, 2013, at 19:21, Natalie Wenz <nataliewenz@ebureau.com> wrote:

> Hi all!
>
> I am working on migrating data in a very large database (22TB +) between two existing tables that use type text for
almostevery field, to two new tables that use native data type for dates, integers, etc. 
>
> The way I'm doing this is running a series of "Insert into new_table values (stuff)… select stuff::appropriate_cast
fromold_table". I'm splitting the data up into chunks using my one index on a date field. 
>
> The problem I'm running into is I keep hitting data that won't cast, and then the insert fails with a message like
this:
>
> ERROR:  invalid input syntax for integer: "X"
>
> I have asked the list how to select for things that are not integer in the past, and the suggestions I got were to
useregular expressions (which seems like it would not do that well performance-wise, with the amount of data I'm
dealingwith), or to dump all the data out and write a program to scrub it. I'm not a terribly experienced programmer,
andI don't have enough extra disk space to dump all of the data, read it in, write out a new copy, and then import it
backinto the database. That would put me at about four copies of the data, rather than two. 
>
> Has anyone else encountered this difficulty? Does anyone have any ideas?

I've been running into similar issues querying an Oracle database and although I never had a chance to implement it
(peopledidn't dare to modify the database that was for a 3rd party application) I did have an idea at the time. 

The idea is to use exception handling to cast what you can to the desired data type. I'm not sure how well exception
handlingscales to a database the size of yours though, considering it creates temporary sub-transactions each time. 

Off the top of my head, implementation in PG would be like this:

CREATE OR REPLACE FUNCTION appropriate_name_here(value text) RETURNS boolean
$$
DECLARE
    intvalue int;
BEGIN
    FOR SELECT stuff FROM old_table LOOP
    BEGIN
        intvalue := stuff.charvalue::int;
    EXCEPT WHEN <invalid input syntax error code here>
        RETURN NEXT false;
    END

    INSERT INTO new_table VALUES (intvalue);
    RETURN NEXT true;

    END LOOP;

    RETURN;
END
$$

I'm a bit rusty on PG stored procedures, you can probably tell :P

You could probably do all your casts to the appropriate tables in one pass using this approach. You probably also want
toadd an INSERT into a failures table for when casts fail, so you can look up the trouble-makers easily. 

Another option is using an ETL tool to extract the data from your database and load it back in into the appropriate
tables.That would work quite similar as the stored procedure approach, but you get more bells and whistles as an
advantage.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.