Re: How to determine offending column for insert exceptions

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.

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

> On Tue, Apr 21, 2015 at 9:48 AM, Shawn Gennaria <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> wrote:
>>
>>> Shawn Gennaria wrote:
>>> > OK, I'm looking at
>>> >
>>> 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.
>
>