Thread: Any hope for more specific error message for "value too long..."?

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi.  If you try to assign a too-long string to a field, Postgresql will say
> so, but won't tell you which value/field is causing the problem:
> CREATE TEMP TABLE foo (a VARCHAR(2));
> INSERT INTO foo VALUES ('ABC');
> CREATE TABLE
> ERROR:  value too long for type character varying(2)
> That doesn't matter much in a simple example like that, but the example
> below is currently making me wish PG was just a little bit more specific.
> Is there much chance of this changing in future releases?

It's an issue that's been on the radar screen for a long time, but it's
not very clear how to improve matters without a lot of added overhead
and/or an API break for user-defined data types, neither of which seem
like prices we'd be willing to pay.  For that matter, it's not totally
clear what would constitute an improvement --- what do you wish it would
show you, exactly?  In the particular case here, the fact that a varchar
length coercion is being invoked isn't even explicit in the query.

Good ideas welcome ...

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I seem to recall a discussion a few years back but cannot find it searching
> online.  The one post I did find was from 6 years ago and I was the only
> respondent and basically said the same or less than I am here.

I dug in the archives and came across a crude POC hack here:

https://www.postgresql.org/message-id/21693.1478376334@sss.pgh.pa.us

At the time I didn't want to pursue it further because of Andres'
pending work on redoing expression execution, but that's landed now.

            regards, tom lane

Dang. +1 for that.

Not that you hadn’t thought of it, and not that it’s actually a viable solution in a jiffy, but switch that mess to
JSONBand your problems are over.  

Ken Tanzer <ken.tanzer@gmail.com> writes:
>>> I dug in the archives and came across a crude POC hack here:
>>> https://www.postgresql.org/message-id/21693.1478376334@sss.pgh.pa.us
>> For that matter, it's not totally
>> clear what would constitute an improvement --- what do you wish it would
>> show you, exactly?

> It looks like that patch is about showing which value or where in the
> statement the error is being caused.  At least for my case, it would be
> helpful to know which field is causing the error.  And just guessing, but
> maybe simpler?

No; read the rest of that thread.  It would actually be nigh impossible to
do it that way in the current system, except for a small subset of cases.
Furthermore, if we did do it like that, what about similar errors in
non-INSERT commands?  The error cursor approach at least has the advantage
of being pretty generically applicable.  In principle we could make it
work for any error arising during expression evaluation.

            regards, tom lane