postgresql - 在 Postgresql TABLESPACE 和 CREATE 命令上出错

Question

我对 Postgres 命令完全陌生。

我有以下 DB2 命令来创建表和表空间:

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#

现在我想在 postgresql 中运行这些命令，我​​已经尝试过

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;

我还有这个 DB2 CREATE TABLE 语句:

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)
)#

我试图将其转换为 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)
)#

来自http://www.sqlines.com/online这个在线网站。运行此命令后，出现类似

的错误

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 (
                     ^

这个转换有什么问题吗？有解决此错误的建议吗？

Answer 1

USER是保留字，需要转义为CREATE TABLE "USER"。

至于您的 CREATE USER 和 CREATE TABLESPACE 命令，那只是错误的语法。例如，在 Postgres 中没有 MANAGED BY。