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 08:38:37 -0700
Message-ID: <55366EFD.3020908@aklaver.com> (raw)
In-Reply-To: <CADx9qBn8R8dXZ8iP0b5kEJbZnDRKfVP8ak7eOuaeCMyYMqwOug@mail.gmail.com>
References: <CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com>
<553665BC.7030400@aklaver.com>
<CADx9qBn8R8dXZ8iP0b5kEJbZnDRKfVP8ak7eOuaeCMyYMqwOug@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 04/21/2015 08:07 AM, Shawn Gennaria wrote:
> 1) 9.4
>
> 2) Everything is contained in a single stored plpgsql function with
> multiple transaction blocks to allow me to debug each stage of the process.
>
> 3) I'm currently handling exceptions with generic 'WHEN OTHERS THEN'
> statements to spit out the SQLSTATE and SQLERRM values to help me figure
> out what's going on. I intend to focus this with statements that catch
> the particular errors that would arise from trying to incorrectly coerce
> my text data into other data types.
From psql.
test=# \d int_test
Table "public.int_test"
Column | Type | Modifiers
----------+-------------------+-----------
int_fld | integer |
var_fld | character varying |
test_col | integer |
test=# insert into int_test values (1, 'test', '2015-04-21'::date);
ERROR: column "test_col" is of type integer but expression is of type date
LINE 1: insert into int_test values (1, 'test', '2015-04-21'::date);
^
HINT: You will need to rewrite or cast the expression.
So the information is there.
The choices would seem to be:
1) Add a bare RAISE to your EXCEPTION block to get the original error to
appear.
http://www.postgresql.org/docs/9.4/interactive/plpgsql-errors-and-messages.html
See the thread below for a similar example;
http://www.postgresql.org/message-id/CAKFQuwbeQBOFPOn1bk9P3uGujMPW13f+hsjjR3D8mJ=jtVAD+A@mail.gmail....
2) Or from here:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPIN...
see 40.6.6.1. Obtaining Information About an Error.
>
> I'm kind of surprised I haven't been able to find answers to this in
> google, though I did see someone else asked a similar question on
> stackoverflow 6 months ago but never got an answer. The best thing I
> can think of right now is to query pg_attributes to find the column
> names for the temp_table I'm dealing with and then loop through each one
> attempting to find a hit on the value that I can see embedded in SQLERRM.
>
> On Tue, Apr 21, 2015 at 10:59 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 04/21/2015 07:39 AM, Shawn Gennaria wrote:
>
> Hi all,
>
> I'm attempting to parse a data set of very many columns from
> numerous
> CSVs into postgres so I can work with them more easily. To this
> end,
> I've created some dynamic queries for table creation, copying
> from CSVs
> into a temp table, and then inserting the data to a final table with
> appropriate data types assigned to each field. 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. Therefore my dynamic
> queries fail
> with 'integer out of range' errors and such. Unfortunately,
> sometimes
> this happens on a file with thousands of columns, and I'd like
> to easily
> figure out which column the erroneous input belongs to without
> having to
> manually scour through it. At this point, the data has already been
> copied into a temp table, so the query producing these errors
> looks like:
>
> INSERT INTO final_table
> SELECT a::int, b::int FROM temp_table
>
> temp_table contains all text fields (since COPY points there and I'd
> rather not debug at that stage), so I'm trying to coerce them to
> more
> appropriate data types with this insert statement.
>
> From this, I'd get an error with SQLSTATE like 22003 and
> SQLERRM like
> 'value "2156947514 <tel:2156947514>" is out of range for type
> integer'. I'd like to be
> able to handle the exception gracefully and modify the data type
> of the
> appropriate column, but I don't know how to determine which column
> contains this data.
>
>
> Not sure, but some more information might help:
>
> 1) What Postgres version?
>
> 2) You mention you are doing this dynamically.
> Where is that happening?
>
> In a stored function?
> If so what language?
>
> In an external program?
>
> 3) How are you handling the exception now?
>
>
>
> I hope this is possible.
>
> Thanks!
> sg
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
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: <55366EFD.3020908@aklaver.com>
Permalink: ../55366EFD.3020908@aklaver.com/
Also on: postgresql.org/message-id/55366EFD.3020908@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: <55366EFD.3020908@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