Re: How to determine offending column for insert exceptions

*value "2156947514" is out of range for type integer*
I suspect that you are using a field of type INTEGER to store a North
American telephone number with its area code. Unfortunately, the maximum
value of an INTEGER field is 2147483647.

If you must use a numeric type for this purpose, use NUMERIC(10), which
will store any ten-digit number.

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