Re: How to determine offending column for insert exceptions

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