Обсуждение: Bad (null) varchar() external representation
Hi all,
I'm getting the following problem when trying to do a simple insert
statement...
"Bad (null) varchar() external representation"
WHY?
I'm running PostgreSQL 7.03 on Linux Mandrake 7.2 (using a specially
compiled version, not an RPM).
Here's the table :
foobar=# \d staff_details Table "staff_details" Attribute | Type | Modifier
----------------+--------------+----------userid | varchar(24) | not nullpassword | char(13) |name
| varchar(96) |role | smallint |dob | date |phone_one | varchar(14)
|phone_two | varchar(14) |phone_three | varchar(14) |address | varchar(280) |status | smallint
|managers_notes | varchar(600) |
Index: staff_details_pkey
Constraints: (length(userid) < 25) (length("password") < 14) (length(name) < 97)
(length(phone_one)< 17) (length(phone_two) < 17) (length(phone_three) < 17)
(length(address)< 281) (length(managers_notes) < 601)
foobar=# insert into staff_details values ('A', NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL);
ERROR: Bad (null) varchar() external representation
foobar=# insert into staff_details (userid, password, name, role, dob,
phone_one) values ('0000111122223333', 'foobarbaz1234', 'Joshua', 1,
'1970-07-01', '(03) 9867 5432');
ERROR: Bad (null) varchar() external representation
foobar=# insert into staff_details values ('0000111122223333',
encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
ERROR: Bad (null) varchar() external representation
etc...
I've tried everything I can think of, also exported and reloaded the
database, etc. This is a new table with nothing in it.
This is driving me nuts. :-(
+ Justin Clift
Database Administrator
Hi,
I haven't seen a mention of a maximum number of constraints of similar
applying to a table. If so, then could someone please point me to it...
The reason I mention this is because I've found what seems to be causing
this problem I'm experiencing with Postgres 7.03 :
CREATE TABLE "staff_details" ( "userid" character varying(24) NOT NULL, "password" character(13) NOT NULL,
"name" character varying(96) NOT NULL, "role" int2 NOT NULL, "dob" date NOT NULL, "phone_one"
charactervarying(14) NOT NULL, "phone_two" character varying(14), "phone_three" character varying(14),
"address" character varying(280), "status" int2, "managers_notes" character varying(600), CONSTRAINT
"staff_details_uesrid"CHECK ((length(userid) < 25)), CONSTRAINT "staff_details_password" CHECK
((length("password")<
14)), CONSTRAINT "staff_details_name" CHECK ((length(name) < 97)), CONSTRAINT "staff_details_dob" CHECK
(date_ge(date(("timestamp"('2001-01-08'::date) - '18 years
00:00'::"interval")), dob)), CONSTRAINT "staff_details_phone_one" CHECK ((length(phone_one) <
17)), CONSTRAINT "staff_details_phone_two" CHECK ((length(phone_two) <
17)), CONSTRAINT "staff_details_phone_three" CHECK
((length(phone_three) < 17)), CONSTRAINT "staff_details_address" CHECK ((length(address) <
281)), CONSTRAINT "staff_details_managers_notes" CHECK
((length(managers_notes) < 601)), PRIMARY KEY ("userid")
);
When I attempt to insert data into this table, I get the following error
:
foobar=# insert into staff_details values ('0000111122223333',
encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
Yet if I remove BOTH the "staff_details_phone_three" &
"staff_details_managers_notes" constraints it works :
foobar=# insert into staff_details values ('0000111122223333',
encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
INSERT 27605472 1
Removing EITHER of these constraints doesn't work, and neither does
removing any of the other constraints on this table. Just these two
TOGETHER. AND they're not even defined one-after-another possibly
indicating some formatting error.
Does anyone have an idea why this is occuring?
Regards and best wishes,
+ Justin Clift
Database Administrator
Justin Clift wrote:
>
> Hi all,
>
> I'm getting the following problem when trying to do a simple insert
> statement...
>
> "Bad (null) varchar() external representation"
>
> WHY?
>
> I'm running PostgreSQL 7.03 on Linux Mandrake 7.2 (using a specially
> compiled version, not an RPM).
>
> Here's the table :
>
> foobar=# \d staff_details
> Table "staff_details"
> Attribute | Type | Modifier
> ----------------+--------------+----------
> userid | varchar(24) | not null
> password | char(13) |
> name | varchar(96) |
> role | smallint |
> dob | date |
> phone_one | varchar(14) |
> phone_two | varchar(14) |
> phone_three | varchar(14) |
> address | varchar(280) |
> status | smallint |
> managers_notes | varchar(600) |
> Index: staff_details_pkey
> Constraints: (length(userid) < 25)
> (length("password") < 14)
> (length(name) < 97)
> (length(phone_one) < 17)
> (length(phone_two) < 17)
> (length(phone_three) < 17)
> (length(address) < 281)
> (length(managers_notes) < 601)
>
> foobar=# insert into staff_details values ('A', NULL, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL);
> ERROR: Bad (null) varchar() external representation
> foobar=# insert into staff_details (userid, password, name, role, dob,
> phone_one) values ('0000111122223333', 'foobarbaz1234', 'Joshua', 1,
> '1970-07-01', '(03) 9867 5432');
> ERROR: Bad (null) varchar() external representation
> foobar=# insert into staff_details values ('0000111122223333',
> encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
> 2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
> ERROR: Bad (null) varchar() external representation
>
> etc...
>
> I've tried everything I can think of, also exported and reloaded the
> database, etc. This is a new table with nothing in it.
>
> This is driving me nuts. :-(
>
> + Justin Clift
> Database Administrator
On Thu, 11 Jan 2001, Justin Clift wrote:
> I haven't seen a mention of a maximum number of constraints of similar
> applying to a table. If so, then could someone please point me to it...
>
> The reason I mention this is because I've found what seems to be causing
> this problem I'm experiencing with Postgres 7.03 :
>
> CREATE TABLE "staff_details" (
> "userid" character varying(24) NOT NULL,
> "password" character(13) NOT NULL,
> "name" character varying(96) NOT NULL,
> "role" int2 NOT NULL,
> "dob" date NOT NULL,
> "phone_one" character varying(14) NOT NULL,
> "phone_two" character varying(14),
> "phone_three" character varying(14),
> "address" character varying(280),
> "status" int2,
> "managers_notes" character varying(600),
> CONSTRAINT "staff_details_uesrid" CHECK ((length(userid) < 25)),
> CONSTRAINT "staff_details_password" CHECK ((length("password") <
> 14)),
> CONSTRAINT "staff_details_name" CHECK ((length(name) < 97)),
> CONSTRAINT "staff_details_dob" CHECK
> (date_ge(date(("timestamp"('2001-01-08'::date) - '18 years
> 00:00'::"interval")), dob)),
> CONSTRAINT "staff_details_phone_one" CHECK ((length(phone_one) <
> 17)),
> CONSTRAINT "staff_details_phone_two" CHECK ((length(phone_two) <
> 17)),
> CONSTRAINT "staff_details_phone_three" CHECK
> ((length(phone_three) < 17)),
> CONSTRAINT "staff_details_address" CHECK ((length(address) <
> 281)),
> CONSTRAINT "staff_details_managers_notes" CHECK
> ((length(managers_notes) < 601)),
> PRIMARY KEY ("userid")
> );
>
> When I attempt to insert data into this table, I get the following error
> :
>
> foobar=# insert into staff_details values ('0000111122223333',
> encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
> 2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
>
Current source seem to insert fine (don't have a 7.0 system to test on
anymore). Also, aren't alot of these length checks meaningless? I think
the values are converted to the correct type first, so the phone number
really shouldn't possibly be longer than 16 since it's a varchar(14).
Justin Clift <aa2@bigpond.net.au> writes:
> "Bad (null) varchar() external representation"
This is a known stupidity in 7.0.* and before: length(varchar) doesn't
like NULLs:
play=> select length(null::varchar);
ERROR: Bad (null) varchar() external representation
It's fixed for 7.1. If it's really bothering you in 7.0.*, find that
error string in src/backend/utils/adt/varchar.c and change the code to
return 0 instead of raising an error for NULL input.
But, as someone else pointed out, the constraint expressions you are
using are redundant anyway, given the declared column length limits.
Getting rid of the constraints might be your easiest workaround for now.
regards, tom lane
Justin Clift <aa2@bigpond.net.au> writes:
> I haven't seen a mention of a maximum number of constraints of similar
> applying to a table. If so, then could someone please point me to it...
There is no such limit that I know of.
> Yet if I remove BOTH the "staff_details_phone_three" &
> "staff_details_managers_notes" constraints it works :
Are you absolutely certain that that's how it went? I think the most
likely story is just that you were hitting the length(varchar)-barfs-
on-NULL bug, and got confused about which combinations you'd tried.
regards, tom lane
Hi Tom, I think you are right. It does seem to barf on NULLS and length(varchar), regardless. Thanks for your assistance. BTW - How do things normally get added to the FAQ? I would like to add something about length(varchar) and NULLS not working in PostgreSQL 7.0.x Regards and best wishes, Justin Clift Database Administrator Tom Lane wrote: > > Justin Clift <aa2@bigpond.net.au> writes: > > I haven't seen a mention of a maximum number of constraints of similar > > applying to a table. If so, then could someone please point me to it... > > There is no such limit that I know of. > > > Yet if I remove BOTH the "staff_details_phone_three" & > > "staff_details_managers_notes" constraints it works : > > Are you absolutely certain that that's how it went? I think the most > likely story is just that you were hitting the length(varchar)-barfs- > on-NULL bug, and got confused about which combinations you'd tried. > > regards, tom lane
Justin Clift <aa2@bigpond.net.au> writes:
> BTW - How do things normally get added to the FAQ?
It's not a FAQ until it's been asked, um, frequently. But you can
try to talk Bruce Momjian into adding it, if you like.
regards, tom lane
Hi Tom and Stephan, Thanks for your help guys. I'm using varchar constraint definitions now that are "CHECK ((foobar ISNULL) OR (length(foobar) < 17))" The short-circuiting of OR's in 7.0.3 allow this to work without error, thereby avoiding the "Bad (null) varchar() external representation" error that I was getting before due to inserting NULL's in length(varchar) constraint checked fields. I've also extended the varchar columns to be the same size as the length checking I'm doing as Tom suggested, to ensure the constraints do work. Tom has also suggested using COALESCE instead, so I'll check this out too. Regards and best wishes, Justin Clift Database Administrator Tom Lane wrote: > > > The reason I'm using constraints in the table is to allow the database > > to recognise when oversize data is being fed to it and generate an > > error, instead of silently accepting the data and truncating it. > > OK, but have you actually stopped to check whether the combination gives > the results you expect? I believe the data will be coerced to the > destination column type --- including any implicit truncation or padding > --- before the constraint expressions are checked. (I further believe > that that's the right order of events.) > > You might need to make the declared column widths one larger than what > the constraints check for, if you want to raise an error for this. > > > I'm just about to try the same constraints with the ISNULL first, incase > > the OR's in postgreSQL are short-circuited like you mention they might > > be. > > I'd suggest > > CHECK (length(COALESCE(column,'')) < n) > > as a workaround for 7.0.*, if you don't want to hack up the source > code as I mentioned. > > regards, tom lane