Message-ID: <55368EE3.9030208@aklaver.com>
Date: Tue, 21 Apr 2015 10:54:43 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.6.0
MIME-Version: 1.0
To: Shawn Gennaria <sgennaria2@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: How to determine offending column for insert exceptions
References: 
 <CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com>
	<553665BC.7030400@aklaver.com>
	<CADx9qBn8R8dXZ8iP0b5kEJbZnDRKfVP8ak7eOuaeCMyYMqwOug@mail.gmail.com>
	<55366EFD.3020908@aklaver.com>
 <CADx9qBkDk9GFeZgmdBBracqtN5w8FsmW69epNP2qKgq9re3gwA@mail.gmail.com>
In-Reply-To: 
 <CADx9qBkDk9GFeZgmdBBracqtN5w8FsmW69epNP2qKgq9re3gwA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql