Thread: Postgresql and scripting

Postgresql and scripting

From
Jerome Lyles
Date:
I took the white space between characters out of my script 'create.txt4' and
ran it on database 'test'.  There were errors:

26
27 -- Create Orders Table
28
29  CREATE TABLE Orders
30 (
31    Order_num    int        NOT NULL,
32    Order_date    datetime    NOT NULL,
33    cust_id    char(10)    NOT NULL
34 );

CREATE TABLE
psql:create.txt4:34: ERROR:  type "datetime" does not exist

Does this mean this datatype doesn't exist on this line (34) or that it
doesn't exist at all?  If it doesn't exist at all what is the correct
datatype to use here?

Also:

66
67 -- Define Foreign Keys
68 ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN
KEY(order_num) REFERENCE Orders(order_num);
69 ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY
(prod_id) REFERENCE Products(prod_id);
70 ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id)
REFERENCE Customers(cust_id);
71 ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY
(vend_id) REFERENCE Vendors(vend_id);

generates these errors:
psql:create.txt4:68: ERROR:  syntax error at or near "REFERENCE" at character
83
psql:create.txt4:69: ERROR:  syntax error at or near "REFERENCE" at character
84
psql:create.txt4:70: ERROR:  syntax error at or near "REFERENCE" at character
77
psql:create.txt4:71: ERROR:  syntax error at or near "REFERENCE" at character
79

What, where is the syntac error??

Re: Postgresql and scripting

From
Tom Lane
Date:
Jerome Lyles <susemail@hawaii.rr.com> writes:
> psql:create.txt4:34: ERROR:  type "datetime" does not exist

> Does this mean this datatype doesn't exist on this line (34) or that it
> doesn't exist at all?  If it doesn't exist at all what is the correct
> datatype to use here?

It doesn't exist at all (any more).  That's an obsolete equivalent for
the more standard datatype TIMESTAMP WITH TIME ZONE.

            regards, tom lane

Re: Postgresql and scripting [SOLVED]

From
Jerome Lyles
Date:
On Wednesday 08 September 2004 02:44 am, tsarevich@gmail.com wrote:
> Use timestamp instead of datetime.
>
> Where you have "REFERENCE", use "REFERENCES"
>
Thank you tsarevich that solved it.  I'm trying to 'SELECT' out of the tables
now but I'm getting another syntax error:-)

test-# SELECT prod_name FROM Products;
ERROR:  syntax error at or near "SELECT" at character 14

What is this error?
Thanks,
Jerome


Re: Postgresql and scripting [SOLVED]

From
Devrim GUNDUZ
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Wed, 8 Sep 2004, Jerome Lyles wrote:

> Thank you tsarevich that solved it.  I'm trying to 'SELECT' out of the tables
> now but I'm getting another syntax error:-)
>
> test-# SELECT prod_name FROM Products;
> ERROR:  syntax error at or near "SELECT" at character 14

  test-# SELECT prod_name FROM Products;
      ^

The dash indicates that another command before this line has been written
but not ended with semicolon. An example:

test=# SELECT
test-# SELECT * from datetest ;
ERROR:  syntax error at or near "SELECT" at characte

You should check the previous line.

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
             http://www.tdmsoft.com
             http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBP1Z4tl86P3SPfQ4RAlw6AKDmamve8PTNClFWnZh/49QxwcsH8wCeM4G3
fEYyFVT5z8DIw5VNgbBudKM=
=TSSn
-----END PGP SIGNATURE-----

Re: Postgresql and scripting [SOLVED]

From
Ben
Date:
Dunno about your version of psql, but in mine, the "-#" prompt means that
you haven't finished your statement yet. "=#" means psql is ready to start
accepting a new statement.

On Wed, 8 Sep 2004, Jerome Lyles wrote:

> On Wednesday 08 September 2004 02:44 am, tsarevich@gmail.com wrote:
> > Use timestamp instead of datetime.
> >
> > Where you have "REFERENCE", use "REFERENCES"
> >
> Thank you tsarevich that solved it.  I'm trying to 'SELECT' out of the tables
> now but I'm getting another syntax error:-)
>
> test-# SELECT prod_name FROM Products;
> ERROR:  syntax error at or near "SELECT" at character 14
>
> What is this error?
> Thanks,
> Jerome
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>



Re: Postgresql and scripting [SOLVED]

From
Jerome Lyles
Date:
On Wednesday 08 September 2004 08:59 am, Devrim GUNDUZ wrote:
> Hi,
>
> On Wed, 8 Sep 2004, Jerome Lyles wrote:
> > Thank you tsarevich that solved it.  I'm trying to 'SELECT' out of the
> > tables now but I'm getting another syntax error:-)
> >
> > test-# SELECT prod_name FROM Products;
> > ERROR:  syntax error at or near "SELECT" at character 14
>
>   test-# SELECT prod_name FROM Products;
>       ^
>
> The dash indicates that another command before this line has been written
> but not ended with semicolon. An example:
>
> test=# SELECT
> test-# SELECT * from datetest ;
> ERROR:  syntax error at or near "SELECT" at characte
>
> You should check the previous line.
>
> Regards,

Thanks, I'll remember this.  The command I ran before was SELECT test * which
I thought would show me all the tables in test.  Running 'SELECT prod_name
FROM Products;' gave me the syntax error and cancelled the previous
instruction.  It worked after I ran it again, along withseveral others.
Thanks again to all,
Jerome