Postgres Table create statement not working

Question

I am working with Postgres DB for the first time and when I try to create my third and last table with foreign keys, I get an error which I don't know how to fix it.

Can anyone help?

CREATE TABLE Customer(
    CUSTID SERIAL PRIMARY KEY,
    Fname CHAR(20),
    Lname CHAR(20), 
    StAdsress CHAR(30), 
    City CHAR(20),
    ProvSt CHAR(2), 
    HPhone CHAR(12),
    WPhone CHAR(12),
    Pemail CHAR(30), 
    Wemail CHAR(30),
    CDate TIMESTAMP NOT NULL
);

CREATE TABLE Catalog (
    CATID SERIAL PRIMARY KEY,
    ItemName CHAR(30),
    ItemDesc CHAR(30), 
    ItemType CHAR(15), 
    ItemWeight numeric(4,2),
    ItemSize CHAR(10), 
    QuantityOnHand INTEGER,
    PRICE numeric(4,2),
    CreateDate TIMESTAMP NOT NULL
);

CREATE TABLE Order (
        ORDID SERIAL NOT NULL,
    CUSTID INT, 
    CATID INT,
    Qty INTEGER,
    OrderDate TIMESTAMP, 
    SubTotal NUMERIC(4,2),
    PRIMARY KEY (ORDID, CUSTID, CATID),
       FOREIGN KEY (CUSTID)
        REFERENCES Customer (CUSTID),
       FOREIGN KEY (CATID)
        REFERENCES Catalog (CATID) 
);

ERROR:  syntax error at or near "Order"
LINE 1: CREATE TABLE Order (
                     ^
SQL state: 42601

djolf · Answer 1 · 2023-04-04T08:42:03.277

0

Order is a reserved word in SQL, so you can't create it directly, SQL will not recognize the table name. You need to surround it with double quotes ".

I should add that generally, it is not a good idea to use a reserved word as table name. You might want to rename it.

CREATE TABLE "Order" (
    ORDID SERIAL NOT NULL,
    CUSTID INT, 
    CATID INT,
    Qty INTEGER,
    OrderDate TIMESTAMP, 
    SubTotal NUMERIC(4,2),
    PRIMARY KEY (ORDID, CUSTID, CATID),
       FOREIGN KEY (CUSTID)
        REFERENCES Customer (CUSTID),
       FOREIGN KEY (CATID)
        REFERENCES Catalog (CATID) 
);

Also, when you make a query to the table you need to use double quotes as well.

Select * from "Order" where ...

edited Apr 04 '23 at 08:42

answered Apr 04 '23 at 08:32

djolf

1,196
6
18

Thank you very much . I completely didn't realize that Order is a SQL term . My bad. – anwarma Apr 04 '23 at 09:01
Yeah, it's used for sorting your data. eg. `select * from TABLE_NAME where bar='foo' order by id desc` – djolf Apr 04 '23 at 09:03

Postgres Table create statement not working

1 Answers1