Thread: Problem in creating a table
Hi Gurus, When I try to create a table by a script I get a error as follows, ##################################################### [postgres@demo pgsql]$ psql -d postgres -e -f /opt/rapisa/sql/TD_ACCESSCOUNT.sql CREATE TABLE TD_ACCESSCOUNT( AC_YEAR NUMBER(4,0) NOT NULL, AC_MONTH NUMBER(2,0) NOT NULL, AC_DAYS NUMBER(2,0) NOT NULL, OFFICE_ID VARCHAR2(7) NOT NULL, AC_COUNT NUMBER(6,0) DEFAULT 0, PRIMARY KEY("AC_YEAR","AC_MONTH","AC_DAYS","OFFICE_ID") ) TABLESPACE RAPIS; psql:/opt/rapisa/sql/TD_ACCESSCOUNT.sql:14: ERROR: syntax error at or near "(" at character 52 ##################################################### I really don't know what is wrong with it and do appreciate it if anyone would point out it. The version of Postgresql is 7.4.7. Thanks in advance. --Wen
On Tue, 17 May 2005 15:47:55 +0900, "Wen Guangcheng" <wen@microcourt.co.jp> wrote: > OFFICE_ID VARCHAR2(7) NOT NULL, This used to be oracle? drop the 2 from the varchar. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
Wen Guangcheng wrote: > Hi Gurus, > > When I try to create a table by a script I get a error as follows, > ##################################################### > [postgres@demo pgsql]$ psql -d postgres -e -f /opt/rapisa/sql/TD_ACCESSCOUNT.sql > > CREATE TABLE TD_ACCESSCOUNT( > AC_YEAR NUMBER(4,0) NOT NULL, > AC_MONTH NUMBER(2,0) NOT NULL, > AC_DAYS NUMBER(2,0) NOT NULL, > OFFICE_ID VARCHAR2(7) NOT NULL, ^^^^^^^^ I don't think there is a varchar2. > AC_COUNT NUMBER(6,0) DEFAULT 0, > PRIMARY KEY("AC_YEAR","AC_MONTH","AC_DAYS","OFFICE_ID") If you're going to quote names, ALWAYS quote them, otherwise let PG fold them to lower-case. -- Richard Huxton Archonet Ltd
Hi Klint, Thanks for your help. But the error message still exists even though the 2 was droped from the varchar. --Wen ----- Original Message ----- From: "Klint Gore" <kg@kgb.une.edu.au> To: "Wen Guangcheng" <wen@microcourt.co.jp> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, May 17, 2005 4:17 PM Subject: Re: [GENERAL] Problem in creating a table > On Tue, 17 May 2005 15:47:55 +0900, "Wen Guangcheng" <wen@microcourt.co.jp> wrote: > > OFFICE_ID VARCHAR2(7) NOT NULL, > > This used to be oracle? drop the 2 from the varchar. > > klint. > > +---------------------------------------+-----------------+ > : Klint Gore : "Non rhyming : > : EMail : kg@kgb.une.edu.au : slang - the : > : Snail : A.B.R.I. : possibilities : > : Mail University of New England : are useless" : > : Armidale NSW 2351 Australia : L.J.J. : > : Fax : +61 2 6772 5376 : : > +---------------------------------------+-----------------+ >
On Tue, 17 May 2005 16:33:33 +0900, "Wen Guangcheng" <wen@microcourt.co.jp> wrote: > Hi Klint, > > Thanks for your help. > But the error message still exists even though the 2 was droped from the varchar. try taking the double quotes out of the primary key (as richard said) and change number to numeric klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
Try this
CREATE TABLE test.TD_ACCESSCOUNT(
AC_YEAR NUMERIC(4,0) NOT NULL,
AC_MONTH NUMERIC(2,0) NOT NULL,
AC_DAYS NUMERIC(2,0) NOT NULL,
OFFICE_ID VARCHAR(7) NOT NULL,
AC_COUNT NUMERIC(6,0) DEFAULT 0,
PRIMARY KEY(AC_YEAR,AC_MONTH,AC_DAYS,OFFICE_ID)
)
CREATE TABLE test.TD_ACCESSCOUNT(
AC_YEAR NUMERIC(4,0) NOT NULL,
AC_MONTH NUMERIC(2,0) NOT NULL,
AC_DAYS NUMERIC(2,0) NOT NULL,
OFFICE_ID VARCHAR(7) NOT NULL,
AC_COUNT NUMERIC(6,0) DEFAULT 0,
PRIMARY KEY(AC_YEAR,AC_MONTH,AC_DAYS,OFFICE_ID)
)
2005/5/17, Wen Guangcheng <wen@microcourt.co.jp>:
Hi Klint,
Thanks for your help.
But the error message still exists even though the 2 was droped from the varchar.
--Wen
----- Original Message -----
From: "Klint Gore" < kg@kgb.une.edu.au>
To: "Wen Guangcheng" <wen@microcourt.co.jp>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, May 17, 2005 4:17 PM
Subject: Re: [GENERAL] Problem in creating a table
> On Tue, 17 May 2005 15:47:55 +0900, "Wen Guangcheng" <wen@microcourt.co.jp > wrote:
> > OFFICE_ID VARCHAR2(7) NOT NULL,
>
> This used to be oracle? drop the 2 from the varchar.
>
> klint.
>
> +---------------------------------------+-----------------+
> : Klint Gore : "Non rhyming :
> : EMail : kg@kgb.une.edu.au : slang - the :
> : Snail : A.B.R.I. : possibilities :
> : Mail University of New England : are useless" :
> : Armidale NSW 2351 Australia : L.J.J. :
> : Fax : +61 2 6772 5376 : :
> +---------------------------------------+-----------------+
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Wen Guangcheng wrote: > Hi Gurus, Hi! > CREATE TABLE TD_ACCESSCOUNT( > AC_YEAR NUMBER(4,0) NOT NULL, > AC_MONTH NUMBER(2,0) NOT NULL, > AC_DAYS NUMBER(2,0) NOT NULL, > OFFICE_ID VARCHAR2(7) NOT NULL, > AC_COUNT NUMBER(6,0) DEFAULT 0, > PRIMARY KEY("AC_YEAR","AC_MONTH","AC_DAYS","OFFICE_ID") > ) > TABLESPACE RAPIS; ^^^^^^^^^^ > psql:/opt/rapisa/sql/TD_ACCESSCOUNT.sql:14: ERROR: syntax error at or near "(" at character 52 > ##################################################### > > I really don't know what is wrong with it and do appreciate it if anyone would > point out it. The version of Postgresql is 7.4.7. > Thanks in advance. Tablespace wasn't supported until 8.0 regards, Robin
Hello, Yes.it works. Thanks a lots to all of you. Cheers, --Wen ----- Original Message ----- From: Dan Black To: Wen Guangcheng Cc: pgsql-general@postgresql.org Sent: Tuesday, May 17, 2005 4:47 PM Subject: Re: [GENERAL] Problem in creating a table Try this CREATE TABLE test.TD_ACCESSCOUNT( AC_YEAR NUMERIC(4,0) NOT NULL, AC_MONTH NUMERIC(2,0) NOT NULL, AC_DAYS NUMERIC(2,0) NOT NULL, OFFICE_ID VARCHAR(7) NOT NULL, AC_COUNT NUMERIC(6,0) DEFAULT 0, PRIMARY KEY(AC_YEAR,AC_MONTH,AC_DAYS,OFFICE_ID) ) 2005/5/17, Wen Guangcheng <wen@microcourt.co.jp>: Hi Klint, Thanks for your help. But the error message still exists even though the 2 was droped from the varchar. --Wen ----- Original Message ----- From: "Klint Gore" < kg@kgb.une.edu.au> To: "Wen Guangcheng" <wen@microcourt.co.jp> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, May 17, 2005 4:17 PM Subject: Re: [GENERAL] Problem in creating a table > On Tue, 17 May 2005 15:47:55 +0900, "Wen Guangcheng" <wen@microcourt.co.jp > wrote: > > OFFICE_ID VARCHAR2(7) NOT NULL, > > This used to be oracle? drop the 2 from the varchar. > > klint. > > +---------------------------------------+-----------------+ > : Klint Gore : "Non rhyming : > : EMail : kg@kgb.une.edu.au : slang - the : > : Snail : A.B.R.I. : possibilities : > : Mail University of New England : are useless" : > : Armidale NSW 2351 Australia : L.J.J. : > : Fax : +61 2 6772 5376 : : > +---------------------------------------+-----------------+ > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Wen Guangcheng wrote: Hi, > TABLESPACE RAPIS; > psql:/opt/rapisa/sql/TD_ACCESSCOUNT.sql:14: ERROR: syntax error at or near "(" at character 52 > I really don't know what is wrong with it and do appreciate it if anyone would > point out it. The version of Postgresql is 7.4.7. > Thanks in advance. Tablespace wasn't supported until 8.0 regards, Robin