Any hope for more specific error message for "value too long..."?
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?
Cheers,
Ken
INSERT INTO tbl_membership_info (client_id,membership_info_
date,survey_schedule_code,hs_homeless_past_code,hs_
homeless_past_skip_code,hs_live_last_30_code,hs_live_
last_30_skip_code,hs_live_last_30_institution,hs_live_last_30_other,hs_live_
satisfied_code,hs_live_satisfied_skip_code,hs_live_
improve_code,hs_live_improve_skip_code,hs_rc_help_improve_
code,hs_rc_help_improve_skip_code,hs_rc_help_maintain_code,
hs_rc_help_maintain_skip_code,da_desire_recovery_code,da_
desire_recovery_skip_code,da_rc_desire_recovery_code,da_rc_
desire_recovery_skip_code,da_in_recovery_alcohol_code,da_
in_recovery_alcohol_skip_code,da_30_alcohol_days,da_30_
alcohol_days_skip_code,da_rc_prevent_alcohol_relapse_code,
da_rc_prevent_alcohol_relapse_skip_code,da_rc_prevent_
alcohol_relapse_how,da_in_recovery_drug_code,da_in_
recovery_drug_skip_code,da_30_drug_days,da_30_drug_days_
skip_code,da_rc_prevent_drug_relapse_code,da_rc_preve
nt_drug_relapse_skip_code,da_rc_prevent_drug_relapse_how,
da_in_recovery_affliction_codes,da_in_recovery_affliction_other,da_in_
recovery_affliction_skip_code,hmh_overall_physical_code,hmh_
overall_physical_skip_code,hmh_physical_care_where_code,
hmh_physical_care_where_other,hmh_physical_care_where_skip_
code,hmh_physical_care_er_90_days,hmh_physical_care_er_90_
days_skip_code,hmh_physical_improve_code,hmh_physical_
improve_skip_code,hmh_rc_physical_improve_code,hmh_rc_
physical_improve_skip_code,hmh_overall_mh_code,hmh_
overall_mh_skip_code,hmh_mh_care_where_code,hmh_mh_care_
where_other,hmh_mh_care_where_skip_code,hmh_mh_care_er_90_
days,hmh_mh_care_er_90_days_skip_code,hmh_rc_mh_improve_
code,hmh_rc_mh_improve_skip_code,hmh_rc_mh_stabilize_code,
hmh_rc_mh_stabilize_skip_code,hmh_rc_access_resource_type_
codes,hmh_rc_access_resource_type_other,hmh_rc_access_
resource_type_skip_code,ad_hope_code,ad_hope_skip_code,
ad_rc_assist_hope_code,ad_rc_assist_hope_skip_code,ad_cope_code,ad_cope_ski
p_code,ad_rc_assist_cope_code,ad_rc_assist_cope_skip_code,
ad_connected_code,ad_connected_skip_code,ad_rc_rc_connected_code,ad_rc_rc_
connected_skip_code,ad_rc_circle_connected_code,ad_rc_
circle_connected_skip_code,ad_rc_connection_type_codes,ad_
rc_connection_type_other,ad_rc_connection_type_skip_code,
comment,added_by,changed_by,sys_log,changed_at) VALUES
('3220','2017-12-26','T4','YES',NULL,'SUBSIDIZED',NULL,
NULL,NULL,'YES',NULL,'YES_ALITTLE',NULL,'NONE',NULL,'
YES_ALITTLE',NULL,'HIGH',NULL,'YES_ALITTLE',NULL,'YES',NULL,
'10',NULL,'NO',NULL,NULL,NULL,'NO_ANSWER',NULL,'NO_ANSWER',
NULL,'NO_ANSWER',NULL,NULL,NULL,'NO_ANSWER','GOOD',NULL,'
DOCTOR',NULL,NULL,NULL,'NO_ANSWER','SOMEWHAT',NULL,'
MEDIUM',NULL,'FAIR',NULL,'COUNSELOR',NULL,NULL,NULL,'NO_
ANSWER','MEDIUM',NULL,'YES',NULL,NULL,NULL,'NO_ANSWER','
HIGH',NULL,'NEUTRAL',NULL,'FAIR',NULL,'NEUTRAL',NULL,'
SOMEWHAT',NULL,'VERY',NULL,'SOMEWHAT',NULL,'{SUPPORT_12,
SUPPORT_AA}',NULL,NULL,NULL,'537','537',NULL,CURRENT_TIMESTAMP) RETURNING
* The PostgreSQL server reported an error.
The error text was: ERROR: value too long for type character varying(10)
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
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
On Fri, Feb 16, 2018 at 5:30 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
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?
I'm not holding my breath...and have to come to feel that when I see that
message in my own production environment I am being punished for defining
an inferior database model. I should have used "text" and if I have length
concerns for storage in tables I should add a check constraint (and
probably be checking for non-visible characters and other stuff too). I
largely am doing that in my new stuff but my legacy schema is not amenable
to such a change - even though removing the type attribute doesn't cause a
table re-write - in particular because of views.
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.
David J.
"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:
/messages/by-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 JSONB and your problems are over.
I dug in the archives and came across a crude POC hack here:
/messages/by-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
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? I'd be happy to see:
The error text was: ERROR *for field [field_name]*: value too long for
type character varying(10)
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
Ken Tanzer <ken.tanzer@gmail.com> writes:
I dug in the archives and came across a crude POC hack here:
/messages/by-id/21693.1478376334@sss.pgh.pa.usFor 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