agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Shawn Gennaria <sgennaria2@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: How to determine offending column for insert exceptions
Date: Tue, 21 Apr 2015 11:05:49 -0700
Message-ID: <5536917D.1020106@aklaver.com> (raw)
In-Reply-To: <CADx9qBkRHaowvdt1yp-pVJ_w608OY1MNr6cZOKTDRFf4BQXmqg@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>
<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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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-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.
>
> 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
view thread (13+ messages)
Message-ID: <5536917D.1020106@aklaver.com>
Permalink: ../5536917D.1020106@aklaver.com/
Also on: postgresql.org/message-id/5536917D.1020106@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, david.g.johnston@gmail.com, alvherre@2ndquadrant.com
Subject: Re: How to determine offending column for insert exceptions
In-Reply-To: <5536917D.1020106@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