On Jun 21, 2013, at 19:21, Natalie Wenz <nataliew...@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 almost every 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 from old_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 use regular expressions (which seems
> like it would not do that well performance-wise, with the amount of data I'm
> dealing with), or to dump all the data out and write a program to scrub it.
> I'm not a terribly experienced programmer, and I 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 back into 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 (people didn'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 handling scales 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 to add 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
