Re: How to determine offending column for insert exceptions - Mailing list pgsql-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
DIAGNOSTICStext_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