agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Shawn Gennaria <sgennaria2@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to determine offending column for insert exceptions
Date: Tue, 21 Apr 2015 10:54:43 -0700
Message-ID: <55368EE3.9030208@aklaver.com> (raw)
In-Reply-To: <CADx9qBkDk9GFeZgmdBBracqtN5w8FsmW69epNP2qKgq9re3gwA@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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-DIA...;
>
> 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
view thread (13+ messages) latest in thread
Message-ID: <55368EE3.9030208@aklaver.com>
Permalink: ../55368EE3.9030208@aklaver.com/
Also on: postgresql.org/message-id/55368EE3.9030208@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, sgennaria2@gmail.com
Subject: Re: How to determine offending column for insert exceptions
In-Reply-To: <55368EE3.9030208@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox