Re: How to determine offending column for insert exceptions

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(at)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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Tue, Apr 21, 2015 at 9:48 AM, Shawn Gennaria
> <sgennaria2(at)gmail(dot)com <mailto:sgennaria2(at)gmail(dot)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(at)2ndquadrant(dot)com <mailto:alvherre(at)2ndquadrant(dot)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(dot)klaver(at)aklaver(dot)com