Обсуждение: Bug #574: can not create tables that start with "Clinic"

Ivan Handler (ivan@jaildoc.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
can not create tables that start with "Clinic"

Long Description
First I logged in to my Linux server (latest RH) as root and did a su -l postgres.  I created a database called chttest
usingcreatedb with no problems.  I then went into psql using chttest and did a \i /var/tmp/CHTdbNew.ddl which is the
scriptI created.  It created all of the tables (28 of them) that do not start with the string, "Clinic" and rejected
the5 that do.  The error message for all 5 was identical: 
-------------------------------------------
ERROR:  parser: parse error at or near "("
-------------------------------------------

the line number was always given as the end of the clause.  I have included the script for you to inspect.  I have been
allthrough this and had another programmer look at it, and we can find no other reason than the name.  It is a pretty
weirdbug or a very subtle script problem that I can not see.  I have just started to use PostGreSQL, so it could be me.
I have enclosed the complete script. 


Sample Code
CREATE TABLE "User"
(
"userid" SERIAL,
"loginName" VARCHAR(20) NOT NULL UNIQUE,
"first" VARCHAR(20),
"last" VARCHAR(30),
"hash" VARCHAR(120) UNIQUE,
"openingScreen" VARCHAR(60),
"crudLevel" VARCHAR(4),
"userid1" INTEGER NOT NULL,
"serviceid" INTEGER NOT NULL,
  CONSTRAINT "PK_User" PRIMARY KEY ("userid")
);


CREATE TABLE "ServiceGroupMembers"
(
"userid" INTEGER NOT NULL,
"serviceid" INTEGER NOT NULL,
  CONSTRAINT "PK_ServiceGroupMembers" PRIMARY KEY ("userid", "serviceid")
);


CREATE TABLE "ServiceGroup"
(
"serviceid" SERIAL,
"serviceName" VARCHAR(50) NOT NULL UNIQUE,
  CONSTRAINT "PK_ServiceGroup" PRIMARY KEY ("serviceid")
);


CREATE TABLE "Providers"
(
"providerid" SERIAL,
"last" VARCHAR(20),
"first" VARCHAR(15),
"type" VARCHAR(20),
  CONSTRAINT "PK_Providers" PRIMARY KEY ("providerid")
);


CREATE TABLE "ServiceGroupProviders"
(
"providerid" INTEGER NOT NULL,
"serviceid" INTEGER NOT NULL,
  CONSTRAINT "PK_ServiceGroupProviders" PRIMARY KEY ("providerid", "serviceid")
);


CREATE TABLE "Inmate"
(
"jailid" VARCHAR(8) NOT NULL,
"dob" INTEGER NOT NULL,
"sex" CHAR(1) NOT NULL,
  CONSTRAINT "PK_Inmate" PRIMARY KEY ("jailid")
);


CREATE TABLE "Alias"
(
"bookingid" VARCHAR(8) NOT NULL,
"last" VARCHAR(30) NOT NULL,
"first" VARCHAR(20),
"housingUnit" VARCHAR(10),
"incarceratonDate" DATE,
"dischargeDate" DATE,
"jailid" VARCHAR(8) NOT NULL,
  CONSTRAINT "PK_Alias" PRIMARY KEY ("bookingid", "jailid")
);


CREATE TABLE "ChronicSubscriptions"
(
"jailid" VARCHAR(8) NOT NULL,
"chronicid" INTEGER NOT NULL,
  CONSTRAINT "PK_ChronicSubscriptions" PRIMARY KEY ("jailid", "chronicid")
);


CREATE TABLE "ChronicDisease"
(
"chronicid" SERIAL,
"name" VARCHAR(25),
  CONSTRAINT "PK_ChronicDisease" PRIMARY KEY ("chronicid")
);


CREATE TABLE "Order"
(
"orderid" SERIAL,
"jailid" VARCHAR(8) NOT NULL,
"dateEntered" DATE NOT NULL,
"dateOrdered" DATE NOT NULL,
"providerid" INTEGER NOT NULL,
"reason" VARCHAR(200),
"note" VARCHAR(200),
"orderType" VARCHAR(20) NOT NULL,
  CONSTRAINT "PK_Order" PRIMARY KEY ("orderid")
);


CREATE TABLE "Lab"
(
"labid" SERIAL,
"dateEntered" DATE NOT NULL,
"scheduleDate" DATE,
"labnameid" INTEGER NOT NULL,
"orderid" INTEGER NOT NULL,
  CONSTRAINT "PK_Lab" PRIMARY KEY ("labid")
);


CREATE TABLE "Labtypes"
(
"labnameid" INTEGER NOT NULL,
"name" VARCHAR(50) NOT NULL UNIQUE,
  CONSTRAINT "PK_Labtypes" PRIMARY KEY ("labnameid")
);


CREATE TABLE "Appointment"
(
"appointmentid" SERIAL,
"dateEntered" DATE NOT NULL,
"orderid" INTEGER NOT NULL,
"appointmentType" VARCHAR(20) NOT NULL,
"scheduleDate" DATE,
"clinicid" INTEGER NOT NULL,
  CONSTRAINT "PK_Appointment" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "Clinic"
(
"clinicid" SERIAL,
"name" VARCHAR(30) NOT NULL,
"cllinicDate" DATE NOT NULL,
"clinicTime" TIME NOT NULL,
"serviceid" INTEGER NOT NULL,
"slots" NUMBER(2),
"clinictypeid" INTEGER NOT NULL,
  CONSTRAINT "PK_Clinic" PRIMARY KEY ("clinicid")
);


CREATE TABLE "Clinicproviders"
(
"clinicid" INTEGER NOT NULL,
"providerid" INTEGER NOT NULL,
"slot" NUMBER(2) NOT NULL,
  CONSTRAINT "PK_Clinicproviders" PRIMARY KEY ("clinicid", "providerid")
);


CREATE TABLE "Externalappointment"
(
"appointmentid" INTEGER NOT NULL,
"serviceproviderid" INTEGER NOT NULL,
  CONSTRAINT "PK_Externalappointment" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "Serviceprovider"
(
"serviceproviderid" SERIAL,
"name" VARCHAR(60) NOT NULL,
"location" VARCHAR(100) NOT NULL,
"subspecialty" VARCHAR(60),
  CONSTRAINT "PK_Serviceprovider" PRIMARY KEY ("serviceproviderid")
);


CREATE TABLE "Labdisposition"
(
"labdispositionid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"reportDate" INTEGER,
"labid" INTEGER NOT NULL,
  CONSTRAINT "PK_Labdisposition" PRIMARY KEY ("labdispositionid")
);


CREATE TABLE "Hospitaldisposition"
(
"appointmentid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"hospitalname" VARCHAR(60),
"dateAdmitted" DATE,
"dateDischarged" DATE,
"disposition" VARCHAR(200),
"diagnosis" VARCHAR(200),
"show" CHAR(1) NOT NULL,
"exitDate" DATE,
"refused" CHAR(1) NOT NULL,
"rescheduled" CHAR(1),
"nextorderid" INTEGER,
  CONSTRAINT "PK_Hospitaldisposition" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "ER"
(
"appointmentid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"hospitalname" VARCHAR(60) NOT NULL,
"diagnosis" VARCHAR(200),
"disposition" VARCHAR(200),
"show" CHAR(1) NOT NULL,
"nextorderid" INTEGER,
"exitDate" DATE,
"refused" CHAR(1) NOT NULL,
"rescheduled" CHAR(1),
"nextorderid1" INTEGER,
  CONSTRAINT "PK_ER" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "offsite"
(
"appointmentid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"disposition" VARCHAR(200),
"show" CHAR(1) NOT NULL,
"exitDate" DATE,
"refused" CHAR(1) NOT NULL,
"rescheduled" CHAR(1),
"nextorderid" INTEGER,
  CONSTRAINT "PK_offsite" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "Infirmary"
(
"appointmentid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"dateAdmitted" DATE,
"dateDischarged" DATE,
"disposition" VARCHAR(200),
"diagnosis" VARCHAR(200),
"show" CHAR(1) NOT NULL,
"exitDate" DATE,
"refused" CHAR(1) NOT NULL,
"rescheduled" CHAR(1),
"nextorderid" INTEGER,
  CONSTRAINT "PK_Infirmary" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "Otherappointment"
(
"appointmentid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"description" VARCHAR(200) NOT NULL,
"scheduleDate" DATE NOT NULL,
"scheduleTime" TIME,
"duration" TIME,
  CONSTRAINT "PK_Otherappointment" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "Clinictype"
(
"clinictypeid" SERIAL,
"name" VARCHAR(30) NOT NULL,
"serviceid" NUMBER(0),
"slots" NUMBER(2),
"rule" RAW(127) NOT NULL,
  CONSTRAINT "PK_Clinictype" PRIMARY KEY ("clinictypeid")
);


CREATE TABLE "Clinictypeproviders"
(
"providerid" INTEGER NOT NULL,
"clinictypeid" INTEGER NOT NULL,
"slot" NUMBER(2) NOT NULL,
  CONSTRAINT "PK_Clinictypeproviders" PRIMARY KEY ("providerid", "clinictypeid")
);


CREATE TABLE "Chronicmonitor"
(
"appointmentid" INTEGER NOT NULL,
"chronicid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"status" VARCHAR(8) NOT NULL,
"control" VARCHAR(8) NOT NULL,
  CONSTRAINT "PK_Chronicmonitor" PRIMARY KEY ("appointmentid", "chronicid")
);


CREATE TABLE "Clinicdisposition"
(
"appointmentid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"providerid" NUMBER(0),
"disposition" VARCHAR(200),
"show" CHAR(1) NOT NULL,
"exitDate" DATE,
"refused" CHAR(1) NOT NULL,
"rescheduled" CHAR(1),
"nextorderid1" INTEGER,
  CONSTRAINT "PK_Clinicdisposition" PRIMARY KEY ("appointmentid")
);


CREATE TABLE "Orderchronic"
(
"orderid" INTEGER NOT NULL,
"chronicid" INTEGER NOT NULL,
  CONSTRAINT "PK_Orderchronic" PRIMARY KEY ("orderid", "chronicid")
);


CREATE TABLE "Reschedule"
(
"appointmentid" INTEGER NOT NULL,
"oldclinicid" INTEGER NOT NULL,
"newclinicid" INTEGER,
"reason" VARCHAR(160) NOT NULL,
"dateEntered" DATE NOT NULL,
  CONSTRAINT "PK_Reschedule" PRIMARY KEY ("appointmentid", "oldclinicid")
);


CREATE TABLE "Request"
(
"requestid" INTEGER NOT NULL,
"jailid" VARCHAR(8) NOT NULL,
"dateOrdered" DATE NOT NULL,
"dateEntered" DATE NOT NULL,
"complaint" VARCHAR(200) NOT NULL,
"disposition" VARCHAR(8),
  CONSTRAINT "PK_Request" PRIMARY KEY ("requestid")
);


CREATE TABLE "AcceptedRequest"
(
"requestid" INTEGER NOT NULL,
"dateEntered" DATE NOT NULL,
"orderid" INTEGER NOT NULL,
  CONSTRAINT "PK_AcceptedRequest" PRIMARY KEY ("requestid", "orderid")
);


CREATE TABLE "Location"
(
"locationcode" VARCHAR(25) NOT NULL,
"description" VARCHAR(60),
"locationtype" VARCHAR(10),
  CONSTRAINT "PK_Location" PRIMARY KEY ("locationcode")
);


No file was uploaded with this report

> Ivan Handler (ivan@jaildoc.com) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> can not create tables that start with "Clinic"

You sure can, but they can't contain a column of data type NUMBER(x),
because there is no such data type.  The RAW(x) data type used elsewhere
also doesn't exist.

--
Peter Eisentraut   peter_e@gmx.net

There is no NUMBER data type. I believe you want to use the NUMERIC data
type.

-Edwin Grubbs

On Wed, 6 Feb 2002 pgsql-bugs@postgresql.org wrote:

> Ivan Handler (ivan@jaildoc.com) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> can not create tables that start with "Clinic"
>
> Long Description
> First I logged in to my Linux server (latest RH) as root and did a su -l postgres.  I created a database called
chttestusing createdb with no problems.  I then went into psql using chttest and did a \i /var/tmp/CHTdbNew.ddl which
isthe script I created.  It created all of the tables (28 of them) that do not start with the string, "Clinic" and
rejectedthe 5 that do.  The error message for all 5 was identical: 
> -------------------------------------------
> ERROR:  parser: parse error at or near "("
> -------------------------------------------
>
> the line number was always given as the end of the clause.  I have included the script for you to inspect.  I have
beenall through this and had another programmer look at it, and we can find no other reason than the name.  It is a
prettyweird bug or a very subtle script problem that I can not see.  I have just started to use PostGreSQL, so it could
beme.  I have enclosed the complete script. 
>
>
> CREATE TABLE "Clinic"
> (
> "clinicid" SERIAL,
> "name" VARCHAR(30) NOT NULL,
> "cllinicDate" DATE NOT NULL,
> "clinicTime" TIME NOT NULL,
> "serviceid" INTEGER NOT NULL,
> "slots" NUMBER(2),
> "clinictypeid" INTEGER NOT NULL,
>   CONSTRAINT "PK_Clinic" PRIMARY KEY ("clinicid")
> );
>