Thread

1. Error in Constraint Checks with PGSQL 7.03

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-11T01:54:31Z

   Justin Clift (aa2@bigpond.net.au) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   Error in Constraint Checks with PGSQL 7.03
   
   Long Description
   Hi,
   
   I'm experiencing this bug 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(16) NOT NULL,
           "phone_two" character varying(16),
           "phone_three" character varying(16),
           "address" character varying(280),
           "status" int2,
           "managers_notes" character varying(600),
           CONSTRAINT "staff_details_userid" 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.
   
   Regards and best wishes,
   
   + Justin Clift
   Database Administrator
   
   
   Sample Code
   
   
   No file was uploaded with this report
   
   
   

2. Re: Error in Constraint Checks with PGSQL 7.03

   Justin Clift <aa2@bigpond.net.au> — 2001-01-11T02:18:37Z

   Sorry,
   
   I somehow missed including the exact error message I get when inserting
   :
   
   Bad (null) varchar() external representation
   
   Regards and best wishes,
   
   Justin Clift
   Database Administrator
   
   pgsql-bugs@postgresql.org wrote:
   > 
   > Justin Clift (aa2@bigpond.net.au) reports a bug with a severity of 2
   > The lower the number the more severe it is.
   > 
   > Short Description
   > Error in Constraint Checks with PGSQL 7.03
   > 
   > Long Description
   > Hi,
   > 
   > I'm experiencing this bug 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(16) NOT NULL,
   >         "phone_two" character varying(16),
   >         "phone_three" character varying(16),
   >         "address" character varying(280),
   >         "status" int2,
   >         "managers_notes" character varying(600),
   >         CONSTRAINT "staff_details_userid" 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.
   > 
   > Regards and best wishes,
   > 
   > + Justin Clift
   > Database Administrator
   > 
   > Sample Code
   > 
   > No file was uploaded with this report