Re: How to determine offending column for insert exceptions - Mailing list pgsql-sql

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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com