Re: How to determine offending column for insert exceptions

1) 9.4

2) Everything is contained in a single stored plpgsql function with
multiple transaction blocks to allow me to debug each stage of the process.

3) I'm currently handling exceptions with generic 'WHEN OTHERS THEN'
statements to spit out the SQLSTATE and SQLERRM values to help me figure
out what's going on. I intend to focus this with statements that catch the
particular errors that would arise from trying to incorrectly coerce my
text data into other data types.

I'm kind of surprised I haven't been able to find answers to this in
google, though I did see someone else asked a similar question on
stackoverflow 6 months ago but never got an answer. The best thing I can
think of right now is to query pg_attributes to find the column names for
the temp_table I'm dealing with and then loop through each one attempting
to find a hit on the value that I can see embedded in SQLERRM.

On Tue, Apr 21, 2015 at 10:59 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>