Message-ID: <55366EFD.3020908@aklaver.com>
Date: Tue, 21 Apr 2015 08:38:37 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 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>
In-Reply-To: 
 <CADx9qBn8R8dXZ8iP0b5kEJbZnDRKfVP8ak7eOuaeCMyYMqwOug@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 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.com


2) Or from here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

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