Question 170585 in PostgreSQL Question posted in
The official documentation can be found here.

“Why do I get psql syntax errors when trying to run an SQL script to create a table in Postgresql?”

I was provided a .sql script containing a query to create a table. I trust the source of the script, so I am wondering if the problem is how/where I’m running it. I’m running it in psql, using a call like i c:/Users/.../sql_script.sql. Here is the code in the script, modified slightly to make it look generic:

CREATE TABLE dbo.mytable (
    id int NOT NULL IDENTITY(1,1),
    userID int NULL,
    Probability DECIMAL(18,8) NULL,
    Conv_Thresh1 int NULL,
    Conv_Thresh2 int NULL,
    DateAddedToDB date NULL
);

ALTER TABLE dbo.mytable ADD CONSTRAINT some_text_i_dont_understand PRIMARY KEY (id);

CREATE NONCLUSTERED INDEX i1 ON mytable  (userID);

And here are the errors I’m seeing:

.sql:8: ERROR:  syntax error at or near "IDENTITY"
LINE 2:  id int NOT NULL IDENTITY(1,1),
                         ^
ALTER TABLE

and

.sql:12: ERROR:  syntax error at or near "NONCLUSTERED"
LINE 1: CREATE NONCLUSTERED INDEX i1 ON mytable  ...

Is this a problem with the script itself? I see similar errors trying to run other "table creation query" sql scripts that have been provided to me, so I’m wondering if it’s more about me not understanding psql/postgres. Thank you

Tags:

2

Answers


  1. 0

    Q: Is this a problem with the script itself?

    In a way, yes. This script is apparently meant for Microsoft SQL Server, whose syntax and features differ from PostgreSQL in many aspects.

    Login or Signup to reply.
  2. 0

    The syntax looks for MSSQL Server. For postgres it would have been like:

    CREATE TABLE mytable (
    id INT GENERATED ALWAYS AS IDENTITY
);

    More info on using identity with postgres.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search