postgresql syntax error when creating a table

Question

Hey everyone I need some help with creating tables. I have the script below and it creates a few tables. When i try to run the script it give me this error:

psql:script.sql:10: ERROR:  syntax error at or near "Group"
LINE 6: CREATE TABLE Group(

Can anyone tell me what is going on?

CREATE TABLE Group(
    name        varchar(40) PRIMARY KEY    NOT NULL
);

CREATE TABLE Artist(
    name        varchar(30) PRIMARY KEY    NOT NULL,
        birthplace  varchar(20)                NOT NULL,
        age     int                        NOT NULL   CHECK (age > 0),
        style       varchar(20)                NOT NULL
);

CREATE TABLE Artwork(
    title      varchar(40) PRIMARY KEY     NOT NULL,
        artist     varchar(30)                 NOT NULL   references Artist(name),
        group_name varchar(40)                 NOT NULL   references Group(name),
        year       int                         NOT NULL   CHECK (year > 0),
        type       varchar(30)                 NOT NULL,
        price      money                       NOT NULL,
);

CREATE TABLE Customer(
    cust_id      int PRIMARY KEY   NOT NULL,
    name         varchar(40)       NOT NULL,
        address      varcahr(60)       NOT NULL,
        amount       money             NOT NULL    CHECK(amount > 0),
        like_artist  varchar(30)       NOT NULL    references Artist(name),
        like_group   varchar(40)       NOT NULL    references Group(name)
);

it is also giving me these errors because of the referential integrity constraint psql:script.sql:16: ERROR: relation "artist" already exists psql:script.sql:25: ERROR: syntax error at or near "group" LINE 4: ...varchar(40) NOT NULL references group(name... ^ psql:script.sql:34: ERROR: syntax error at or near "group" LINE 7: ..._group varchar(40) NOT NULL references group(name... — alpal
– alpal, Commented Feb 13, 2014 at 22:23
Group is a reserved keyword in SQL, and needs to be quoted as "Group". — Joachim Isaksson
– Joachim Isaksson, Commented Feb 13, 2014 at 22:24
shit, it should throws reserved keyword error instead of syntax error — vanduc1102
– vanduc1102, Commented Aug 3, 2018 at 2:25

Mark Giaconia · Accepted Answer · 2014-02-13 22:29:52Z

41

it had a lot of problems this worked for me. In postgres, names that are not all lowercase need to be double quoted. also some of your table names are reserved words, money can't be > and int, and there was a comma out of place.

CREATE TABLE "group"( name varchar(40) PRIMARY KEY NOT NULL );

CREATE TABLE artist( name varchar(30) PRIMARY KEY NOT NULL, birthplace varchar(20) NOT NULL, age int NOT NULL CHECK (age > 0), style varchar(20) NOT NULL );

CREATE TABLE artwork( title varchar(40) PRIMARY KEY NOT NULL, artist varchar(30) NOT NULL references artist(name), 
group_name varchar(40) NOT NULL references "group"(name), year int NOT NULL CHECK (year > 0), type varchar(30) NOT NULL, price money NOT NULL );

CREATE TABLE customer( cust_id int PRIMARY KEY NOT NULL, name varchar(40) NOT NULL, address varchar(60) NOT NULL, 
amount money NOT NULL CHECK(amount > cast(0.0 as money)), like_artist varchar(30) NOT NULL references artist(name), like_group varchar(40) NOT NULL references "group"(name) );

answered Feb 13, 2014 at 22:29

Mark Giaconia

3,9716 gold badges22 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Russo Jun 1 at 9:25

CREATE DATABASE "db_name1";

Collectives™ on Stack Overflow

postgresql syntax error when creating a table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related