Weird syntax error in PSQL while creating table

Question

I am trying to create a table in a PSQL database with VSCode, but I receive syntax error. INT is underlined by vscode, if I delete it then TEXT and so on... Could you please give me any idea what is the issue?

CREATE TABLE nfl_week_three (
    rank INT,
    team TEXT,
    games INT,
    points_scored INT,
    total_yards INT,
    offensive_plays INT,
    yards_per_play DOUBLE PRECISION,
    turnover_lost INT,
    fumbles_lost INT,
    1st_downs INT,
    passes_completed INT,
    passes_attempted INT,
    passing_yards INT,
    passing_touchdowns INT,
    passing_interceptions INT,
    net_yards_per_passing_attempt DOUBLE PRECISION,
    passing_1st_downs INT,
    rushing_attempts INT,
    rushing_yards INT,
    rushing_touchdowns INT,
    rushing_yards_per_attempt DOUBLE PRECISION,
    rushing_1st_downs INT,
    penalties INT,
    penalty_yards INT,
    1st_down_penalties INT,
    percetage_scoring_drives INT,
    percentage_turnover_drives INT,
    expected_points INT
);

My curiosity, why those are double precision? Int would be fine, no? — Cetin Basoz
– Cetin Basoz, Commented Oct 5, 2022 at 22:37
Probably you should try another editor than VSCode. Like Dbeaver, pgAdmin4, HeidiSQL ... — Cetin Basoz
– Cetin Basoz, Commented Oct 5, 2022 at 22:42
I used double integers since these are decimal numbers. Actually VSCode is working well with SQL, I made a mistake with the column names. — Milan Jakab
– Milan Jakab, Commented Oct 5, 2022 at 23:10
FYI, you are creating a table in a Postgres(ql) database. psql is the Postgres command client. — Adrian Klaver
– Adrian Klaver, Commented Oct 6, 2022 at 4:54
I see, but wouldn't a decimal would be more appropriate? decimal is exact while double precision is not and their rounding behaviors are different. — Cetin Basoz
– Cetin Basoz, Commented Oct 6, 2022 at 11:19

Bill Karwin · Accepted Answer · 2022-10-05 23:08:54Z

3

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS says:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).

This means your column name 1st_downs and others that start with a digit are a problem.

You can either change the column names, or else delimit such names in double-quotes.

edited Oct 5, 2022 at 23:08

answered Oct 5, 2022 at 22:43

Bill Karwin

567k87 gold badges710 silver badges871 bronze badges

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

Comments

Cetin Basoz · Accepted Answer · 2022-10-05 22:37:03Z

You just had some extra 1's in there as typo.

CREATE TABLE nfl_week_three
(
    rank                          INT,
    team                          TEXT,
    games                         INT,
    points_scored                 INT,
    total_yards                   INT,
    offensive_plays               INT,
    yards_per_play                DOUBLE PRECISION,
    turnover_lost                 INT,
    fumbles_lost                  INT,
    st_downs                      INT,
    passes_completed              INT,
    passes_attempted              INT,
    passing_yards                 INT,
    passing_touchdowns            INT,
    passing_interceptions         INT,
    net_yards_per_passing_attempt DOUBLE PRECISION,
    passing_1st_downs             INT,
    rushing_attempts              INT,
    rushing_yards                 INT,
    rushing_touchdowns            INT,
    rushing_yards_per_attempt     DOUBLE PRECISION,
    rushing_1st_downs             INT,
    penalties                     INT,
    penalty_yards                 INT,
    st_down_penalties             INT,
    percetage_scoring_drives      INT,
    percentage_turnover_drives    INT,
    expected_points               INT
);

Collectives™ on Stack Overflow

Weird syntax error in PSQL while creating table

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