Possible bug? WAS :Bad (null) varchar() external representation. - Mailing list pgsql-sql

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