Re: How to determine offending column for insert exceptions

OK, I'm looking at
www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES
which I completely missed before. This sounds like my answer, but it's not
returning anything when I try to extract the COLUMN_NAME. In desperation,
I tried grabbing every value in that table, and it just repeats the same
info I already had via SQLSTATE and SQLERRM.

Here's what my overall implementation looks like:

DECLARE
col_name text;
sql_state text;
...
BEGIN
FOR rec IN (
SELECT 1 file per row: info about each of my csv files to dynamically
build the tables, copy and insert data
) LOOP
...
QRY_INSERT := 'INSERT INTO rec.final_table SELECT rec.inserts FROM
rec.temp_table'; -- rec.inserts is text formed like 'a::int, b::int,...'
BEGIN
EXECUTE QRY_INSERT;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS col_name = COLUMN_NAME, sql_state =
RETURNED_SQLSTATE ...etc...
RAISE INFO '%, %, ......', col_name, sql_state, ......;
END;
END LOOP;
END;

The only values I get back are:
RETURNED_SQLSTATE = 22003
MESSAGE_TEXT = 'value "2156947514" is out of range for type integer
PG_EXCEPTION_CONTEXT = SQL statement

The rest are null. I'm confused why your error message was more
informative.

I tried leaving everything else out of the exception and just using a bare
RAISE like you said, but that just put the same exact error message out to
my Messages tab in pgAdmin-- no mention of any columns.

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

> On 04/21/2015 08:07 AM, Shawn Gennaria wrote:
>
>> 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.
>>
>
> From psql.
>
> test=# \d int_test
> Table "public.int_test"
> Column | Type | Modifiers
> ----------+-------------------+-----------
> int_fld | integer |
> var_fld | character varying |
> test_col | integer |
>
>
>
> test=# insert into int_test values (1, 'test', '2015-04-21'::date);
> ERROR: column "test_col" is of type integer but expression is of type date
> LINE 1: insert into int_test values (1, 'test', '2015-04-21'::date);
> ^
> HINT: You will need to rewrite or cast the expression.
>
> So the information is there.
>
> The choices would seem to be:
>
> 1) Add a bare RAISE to your EXCEPTION block to get the original error to
> appear.
>
>
>
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-errors-and-messages.html
>
>
> See the thread below for a similar example;
>
>
> http://www.postgresql.org/message-id/CAKFQuwbeQBOFPOn1bk9P3uGujMPW13f+hsjjR3D8mJ=jtVAD+A@mail.gmail.com
>
>
> 2) Or from here:
>
>
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> see 40.6.6.1. Obtaining Information About an Error.
>
>
>> 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 <mailto: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 <tel: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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>