Re: How to determine offending column for insert exceptions - Mailing list pgsql-sql
From | Adrian Klaver |
---|---|
Subject | Re: How to determine offending column for insert exceptions |
Date | |
Msg-id | 55368EE3.9030208@aklaver.com Whole thread Raw |
In response to | Re: How to determine offending column for insert exceptions (Shawn Gennaria <sgennaria2@gmail.com>) |
List | pgsql-sql |
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