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(at)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(at)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(at)test=# insert into int_test select v_fld::int from source_tbl;
ERROR: value "2156947514" is out of range for type integer

postgres(at)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(dot)klaver(at)aklaver(dot)com