PostgreSQL12和openGuass/MogDB3.1.0在CREATE SEQUENCE上的不同点

原作者：姜殿斌

适用范围

PostgreSQL12，openGuass3.10、MogDB3.1.0

问题概述

​ 需要使用PostgreSQL12导出sql格式的备份，在MogDB3.1.0上执行导入,遇到：

gsql:postgres_t1.sql:175: ERROR: syntax error at or near “AS”
LINE 2: AS integer
^

查看原始语句：

CREATE SEQUENCE postgres_t1_account_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

问题原因

对比两种数据库的创建序列的语法区别：

1、PostgreSQL中创建序列的语法：

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name [ AS data_type ] [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ]
其中：
data_type

The optional clauseAS*data_type*specifies the data type of the sequence. Valid types aresmallint,integer, andbigint.bigintis the default. The data type determines the default minimum and maximum values of the sequence.

2、MogDB中创建序列的语法：

CREATE [ LARGE ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE | NOMINVALUE] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE | NOCYCLE]
[ OWNED BY { table_name.column_name | NONE } ];

也就是MogDB不支持：AS integer这种写法。

CreateSequence ::= CREATE [ LARGE ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE | NOMINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE | NOCYCLE ] [ OWNED BY { table_name’.'column_name | NONE } ];

解决方案

删除脚本中的 AS integer这行。

CREATE SEQUENCE postgres_t1_account_id_seq

START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

导入正常。

另外，pg中pg_dump导出的备份，使用gs_restore无法导入。

参考文档

https://www.postgresql.org/docs/current/sql-createsequence.html

https://docs.mogdb.io/zh/mogdb/v3.1/CREATE-SEQUENCE#%E8%AF%AD%E6%B3%95%E6%A0%BC%E5%BC%8F