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