Incorrect Syntax on a Table in SQL

Question

I'm building a database for a site and I keep getting an incorrect syntax error for this portion of it:

    CREATE TABLE posts (
        pid SERIAL PRIMARY KEY,
        title VARCHAR(255),
        body VARCHAR,
        user_id INT REFERENCES users(uid),
        author VARCHAR REFERENCES users(username),
        date_created TIMESTAMP
        like_user_id INT[] DEFAULT ARRAY[]::INT,
        likes INT DEFAULT
    );

When I put a comma after TIMESTAMP, it then switches the error message to after the INT [] on the like_user_id line.

Tag your question with the database you are using. The syntax is definitely not standard SQL. Also, your foreign key references do not make sense. Why would you have separate columns for user_id and author with two references to the same table using different columsn? — Gordon Linoff
– Gordon Linoff, Commented Dec 28, 2020 at 21:40

Gordon Linoff · Accepted Answer · 2020-12-28 21:43:02Z

1

Assuming you are using Postgres (which the syntax is closest to), you can use something like this:

CREATE TABLE posts (
    pid SERIAL PRIMARY KEY,
    title VARCHAR(255),
    body VARCHAR(255),
    author_uid INT REFERENCES users(uid),
    date_created TIMESTAMP,
    like_user_id INT[] DEFAULT '{}'::INT[],
    likes INT DEFAULT 0
);

Notes:

There is no need for a separate foreign key reference for the user and the name. Use a join to get the name.
You need a comma after the timestamp.
You need an appropriate expression for an empty array for like_user_id.
You need a default value for likes.

I also recommend putting in lengths for the varchar(). I mean, you can skip all the lengths if you like (Postgres allows that), but I don't see a reason to have lengths in some columns and no lengths in others.

answered Dec 28, 2020 at 21:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Lukasz Szozda · Accepted Answer · 2020-12-28 21:44:13Z

0

Cast ARRAY[]::INT => ARRAY[]::INT[]:

 CREATE TABLE posts (
        pid SERIAL PRIMARY KEY,
        title VARCHAR(255),
        body VARCHAR,
        user_id INT REFERENCES users(uid),
        author VARCHAR REFERENCES users(username),
        date_created TIMESTAMP,
        like_user_id INT[] DEFAULT array[]::int[],
        likes INT DEFAULT 0
    );

db<>fiddle demo

answered Dec 28, 2020 at 21:44

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Incorrect Syntax on a Table in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related