Re: How to determine offending column for insert exceptions

On 04/21/2015 07:39 AM, Shawn Gennaria wrote:
> Hi all,
>
> I'm attempting to parse a data set of very many columns from numerous
> CSVs into postgres so I can work with them more easily. To this end,
> I've created some dynamic queries for table creation, copying from CSVs
> into a temp table, and then inserting the data to a final table with
> appropriate data types assigned to each field. The majority of the data
> can fit into integer fields, but occasionally I hit some entries that
> need to be text or bigint or floats. Therefore my dynamic queries fail
> with 'integer out of range' errors and such. Unfortunately, sometimes
> this happens on a file with thousands of columns, and I'd like to easily
> figure out which column the erroneous input belongs to without having to
> manually scour through it. At this point, the data has already been
> copied into a temp table, so the query producing these errors looks like:
>
> INSERT INTO final_table
> SELECT a::int, b::int FROM temp_table
>
> temp_table contains all text fields (since COPY points there and I'd
> rather not debug at that stage), so I'm trying to coerce them to more
> appropriate data types with this insert statement.
>
> From this, I'd get an error with SQLSTATE like 22003 and SQLERRM like
> 'value "2156947514" is out of range for type integer'. I'd like to be
> able to handle the exception gracefully and modify the data type of the
> appropriate column, but I don't know how to determine which column
> contains this data.

Not sure, but some more information might help:

1) What Postgres version?

2) You mention you are doing this dynamically.
Where is that happening?

In a stored function?
If so what language?

In an external program?

3) How are you handling the exception now?

>
> I hope this is possible.
>
> Thanks!
> sg

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com