CODEWITHSUNDEEP
postgresqlerror-handlingstaging
Dave Tapson
Dave Tapson

Reputation: 850

Postgresql - moving data from loosely typed staging table to strongly typed final table - and catch errors

I have a staging table that is loosely typed (all cols (except pk) of datatype text) and I want to move this data into a table that is correctly typed for the data e.g. numerics are numerics, dates are dates etc.

My initial approach is to create a cursor in pgsql and loop through the data inserting it into the final table with appropriate casts.

I will catch errors and use them to insert rows that fail into an errors table that I can look at and address later.

Is there a cleverer way of doing this?

Regards Dave

Upvotes: 0

Views: 630

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324771

Use a PL/PgSQL function with a BEGIN ... EXCEPTION WHEN ... END block wrapping the operation(s) that might fail.

See Postgres sql exception handling for batch insert for an example, and caveats.

It'd be nice if PostgreSQL's cast functions could be called in a test mode, where they return a boolean result indicating success and discard the product, or even better return a tuple of (result, successflag). That way you could do it all with plain SQL. Unfortunately they don't support that, so you've got to use an exception handling loop and subtransactions.

Upvotes: 1

Related Questions