CODEWITHSUNDEEP
postgresql
vkp_stack
vkp_stack

Reputation: 157

Getting error on Postgresql TABLESPACE and CREATE commands

I am completely new in Postgres commands.

I have the following DB2 commands to create tables and table spaces:

CREATE USER TEMPORARY TABLESPACE MYSPACE MANAGED BY AUTOMATIC STORAGE#

CREATE TABLESPACE SYSTOOLSPACE MANAGED BY AUTOMATIC STORAGE#

CREATE SEQUENCE REVISION AS BIGINT START WITH 1 INCREMENT BY 1 MAXVALUE 4611686018427387903 CYCLE CACHE 1000#

Now i want to run these commands in postgresql , I have tried with 

my_db=# CREATE USER TEMPORARY TABLESPACE MYSPACE MANAGED BY AUTOMATIC STORAGE;
ERROR:  syntax error at or near "TABLESPACE"
LINE 1: CREATE USER TEMPORARY TABLESPACE MYSPACE MANAGED BY AUTO...


my_db=# CREATE TABLESPACE SYSTOOLSPACE MANAGED BY AUTOMATIC STORAGE;
ERROR:  syntax error at or near "MANAGED"
LINE 1: CREATE TABLESPACE SYSTOOLSPACE MANAGED BY AUTOMATIC STORAGE;

I also have this DB2 CREATE TABLE statement:

CREATE TABLE USER (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 100),
  E_VER BIGINT NOT NULL,
  NAME VARCHAR(38) NOT NULL UNIQUE,
  EMAIL_ADDRESS VARCHAR(255) NOT NULL,
  PASSWORD VARCHAR(32) NOT NULL,
  SUPER_ADMIN SMALLINT NOT NULL,
  MAIN_ADMIN SMALLINT NOT NULL,
  SERVER_ADMIN SMALLINT NOT NULL,
  GROUP_ADMIN SMALLINT NOT NULL,
  CLIENT_ADMIN SMALLINT NOT NULL,
  ENABLED SMALLINT NOT NULL,
  HIDDEN SMALLINT NOT NULL,
  PRIMARY KEY (ID)
)#

and I have tried to convert this to Postgres:

CREATE SEQUENCE USER_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE USER (
  ID BIGINT DEFAULT NEXTVAL ('USER_seq'),
  E_VER BIGINT NOT NULL,
  NAME VARCHAR(38) NOT NULL UNIQUE,
  EMAIL_ADDRESS VARCHAR(255) NOT NULL,
  PASSWORD VARCHAR(32) NOT NULL,
  SUPER_ADMIN SMALLINT NOT NULL,
  MAIN_ADMIN SMALLINT NOT NULL,
  SERVER_ADMIN SMALLINT NOT NULL,
  GROUP_ADMIN SMALLINT NOT NULL,
  CLIENT_ADMIN SMALLINT NOT NULL,
  ENABLED SMALLINT NOT NULL,
  HIDDEN SMALLINT NOT NULL,
  PRIMARY KEY (ID)
)#

by http://www.sqlines.com/online this online site. After running this command i am getting error like 

my_db=# CREATE SEQUENCE USER_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE
my_db=#
my_db=#
my_db=# CREATE TABLE USER (
my_db(#   ID BIGINT DEFAULT NEXTVAL ('USER_seq'),
my_db(#   E_VER BIGINT NOT NULL,
my_db(#   NAME VARCHAR(38) NOT NULL UNIQUE,
my_db(#   EMAIL_ADDRESS VARCHAR(255) NOT NULL,
my_db(#   PASSWORD VARCHAR(32) NOT NULL,
my_db(#   SUPER_ADMIN SMALLINT NOT NULL,
my_db(#   MAIN_ADMIN SMALLINT NOT NULL,
my_db(#   SERVER_ADMIN SMALLINT NOT NULL,
my_db(#   GROUP_ADMIN SMALLINT NOT NULL,
my_db(#   CLIENT_ADMIN SMALLINT NOT NULL,
my_db(#   ENABLED SMALLINT NOT NULL,
my_db(#   HIDDEN SMALLINT NOT NULL,
my_db(#   PRIMARY KEY (ID)
my_db(# );
ERROR:  syntax error at or near "USER"
LINE 1: CREATE TABLE USER (
                     ^

Anything wrong this conversion? Any suggestion solve this error?

Upvotes: 1

Views: 1157

Answers (1)

Kayaman
Kayaman

Reputation: 73558

USER is a reserved word, you need to escape it as CREATE TABLE "USER".

As for your CREATE USER and CREATE TABLESPACE commands, that's just wrong syntax. There's no MANAGED BY in Postgres for example.

Upvotes: 2

Related Questions