Getting error on Postgresql TABLESPACE and CREATE commands

Question

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?

Why do you think that DB2 commands are valid in Postgres? Those are two different products. Please refer to the Postgres manual, not some random internet page for the correct syntax: postgresql.org/docs/current/static/index.html — user330315
– user330315, Commented Oct 25, 2017 at 7:15
I just want to convert DB2 commands to postgresql . I know that are two different products . — vkp_stack
– vkp_stack, Commented Oct 25, 2017 at 7:16

Kayaman · Accepted Answer · 2017-10-25 07:03:09Z

2

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.

answered Oct 25, 2017 at 7:03

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

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

5 Comments

Kayaman Over a year ago

Postgres isn't DB2. postgresql.org/docs/current/static/sql-createtablespace.html

vkp_stack Over a year ago

Thanks for support . Can you please look on the update of my question . getiing error `syntax error at or near "ENFORCED"' . is this also reserved commands ?

Kayaman Over a year ago

No, it's again wrong syntax. I don't know why you think you can run DB2 commands on Postgres, I already said Postgres isn't DB2.

vkp_stack Over a year ago

Ok . I have convert from Ibm DB2 to Postggresql with sqlines.com/online this online tool . I didn't run DB2 commands directly . Is this invalid conversion ?

Kayaman Over a year ago

There's no ENFORCED in Postgres.

Collectives™ on Stack Overflow

Getting error on Postgresql TABLESPACE and CREATE commands

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related