Re: How to determine offending column for insert exceptions

On 04/21/2015 09:37 AM, Shawn Gennaria wrote:
> OK, I'm looking at
> www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES
>
<http://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.
>
>

Yeah I saw the below and did not pay enough attention to the actual 
error you posted.

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

So as David said this is a parser error, though:

postgres@test=# \d int_test    Table "public.int_test" Column  |  Type   | Modifiers
---------+---------+----------- int_fld | integer |
       Table "public.source_tbl" Column |       Type        | Modifiers
--------+-------------------+----------- v_fld  | character varying |

postgres@test=# insert into int_test values ('2156947514'::int);
ERROR:  value "2156947514" is out of range for type integer
LINE 1: insert into int_test values ('2156947514'::int);


postgres@test=# insert into int_test select v_fld::int from source_tbl;
ERROR:  value "2156947514" is out of range for type integer

postgres@test=# select '2156947514'::int;
ERROR:  value "2156947514" is out of range for type integer
LINE 1: select '2156947514'::int;

Only part of the error in the SELECT is pushed up to the INSERT error.


The only thing I can think to do is pre-test the data in the CSV or the 
temp table for type, instead of letting the parser do it. One program I 
can point you at, assuming you are comfortable with Python,  is 
ddl-generator:

https://github.com/catherinedevlin/ddl-generator

I have used it on smaller datasets(number of columns) then you are 
working on, so I can't say how it will scale to your case.

-- 
Adrian Klaver
adrian.klaver@aklaver.com