Message-ID: <5536917D.1020106@aklaver.com>
Date: Tue, 21 Apr 2015 11:05:49 -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>,
 "David G. Johnston" <david.g.johnston@gmail.com>
CC: Alvaro Herrera <alvherre@2ndquadrant.com>,
 "pgsql-sql@postgresql.org" <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>
	<20150421164635.GE4369@alvh.no-ip.org>
	<CADx9qBnUOV964nhA=s2XZnpsrc=nJWx-rS4QUXjp1aHLKNDbpA@mail.gmail.com>
	<CAKFQuwam1+-cTXFqWhJdi_NFY1A5Yh4TGK2hUBcps-e_+R6BRw@mail.gmail.com>
 <CADx9qBkRHaowvdt1yp-pVJ_w608OY1MNr6cZOKTDRFf4BQXmqg@mail.gmail.com>
In-Reply-To: 
 <CADx9qBkRHaowvdt1yp-pVJ_w608OY1MNr6cZOKTDRFf4BQXmqg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 04/21/2015 10:54 AM, Shawn Gennaria wrote:
> David,
>
> Thanks for the insight.  Indeed, I could not replicate Adrian's error
> message by substituting his date example in my code.  It just gives me a
> generic 'cannot cast type date to integer' with no mention of a column
> name.  I think I better understand how the context affects the ability
> to provide certain information in error messages.

DO
$$
DECLARE
     text_var1 text;
     text_var2 text;
     text_var3 text;
BEGIN
   insert into int_test values (1, 'test', '2015-04-21'::date);
   EXCEPTION WHEN OTHERS THEN
     GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                           text_var2 = PG_EXCEPTION_DETAIL,
                           text_var3 = PG_EXCEPTION_HINT;
     RAISE NOTICE '%, %, %', text_var1, text_var2, text_var3;
END$$;


postgres@test=# \e
NOTICE:  column "test_col" is of type integer but expression is of type 
date, , You will need to rewrite or cast the expression.
DO

>
> I'll attempt to solve my problem by querying pg_attribute for the
> columns of the table I'm dealing with and then I'll just loop over them
> all until I get a hit on the value returned in my error message.  It
> won't be pretty, but it's better than browsing thousands of columns in
> CSVs trying to find these pitfalls.
>
> Thank you all for the assist!
>
>
> On Tue, Apr 21, 2015 at 1:04 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>     On Tue, Apr 21, 2015 at 9:48 AM, Shawn Gennaria
>     <sgennaria2@gmail.com <mailto:sgennaria2@gmail.com>>wrote:
>
>         Glad to hear it's here just in time.  I am using 9.4, though, so
>         I wish I could figure out why it's returning NULL when I use
>         it.  And the error message string doesn't contain any column
>         name to parse in my output.
>
>         On Tue, Apr 21, 2015 at 12:46 PM, Alvaro Herrera
>         <alvherre@2ndquadrant.com <mailto:alvherre@2ndquadrant.com>> wrote:
>
>             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.
>
>             As far as I recall, COLUMN_NAME is new in 9.4.  If you're
>             trying with an
>             earlier version, you can't get that info other than by
>             parsing the error
>             message string.
>
>
>     ​Adrian provided an example of trying to place a valid date into a
>     non-date column.  The data itself was correct but the place it is
>     being stored to is invalid and can be reported explicitly.
>
>     Shawn provided an example of trying to create an integer using an
>     invalid value.  Type input errors are not column specific and so the
>     error - which is basically a parse error - does not provide column
>     information.
>
>     There is likely some more experimenting here, and maybe room to
>     attached optional contextual markers to make better error messages,
>     but fundamentally these are two different kinds of errors which
>     cannot be generalized over to the extent of saying "errors should
>     provide column name information"...because what would you do for {
>     SELECT 'a'::int }?
>
>     David J.
>
>


-- 
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