Problem in creating a table

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

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             :                 :
+---------------------------------------+-----------------+

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 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)
)

2005/5/17, Wen Guangcheng <wen@microcourt.co.jp>:

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

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