openGauss兼容PG语法吗？

原创贺晓群 2020-07-28

7691

openGauss是基于PostgreSQL9.2.4的内核开发的，改造很大，例如把多进程改成了单进程多线程，XID改为了64位，流复制并发恢复、增加了列存和内存表等等，但是同时也失去了PG简单、易用、实用的新特性和灵活的第三方扩展。

下面简单列举一些从PG迁移到openGauss开发需要注意的坑:

1、openGauss中对象名不能直接使用关键字

openGauss中对象名不能直接使用关键字，如果要使用，必须双引号包裹，但是在PG中可以直接使用

--openGauss，less为关键字
postgres=# CREATE TABLE test(less INT);
ERROR:  syntax error at or near "less"
LINE 1: CREATE TABLE test(less INT);
                          ^
postgres=# CREATE TABLE test("less" INT);
CREATE TABLE
postgres=# SELECT * FROM test;
 less 
------
(0 rows)

postgres=# SELECT less FROM test;
ERROR:  syntax error at or near "less"
LINE 1: SELECT less FROM test;
               ^
postgres=# SELECT "less" FROM test;
 less 
------
(0 rows)

--PostgreSQL10.3
postgres=# CREATE TABLE test(less INT);
CREATE TABLE
postgres=# SELECT * FROM test;
 less 
------
(0 rows)

postgres=# SELECT less FROM test;
 less 
------
(0 rows)

postgres=# SELECT "less" FROM test;
 less 
------
(0 rows)

2、openGauss目前发布的版本不支持外键

openGauss目前发布的版本不支持外键，据了解下个版本会支持

--openGauss
postgres=# CREATE TABLE p_test(pid INT PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p_test_pkey" for table "p_test"
CREATE TABLE
postgres=# CREATE TABLE c_test(cid INT,pid INT,CONSTRAINT fk_pid_c_test_p_test FOREIGN KEY (pid) REFERENCES p_test(pid));
ERROR:  FOREIGN KEY ... REFERENCES constraint is not yet supported.

--PostgreSQL10.3
postgres=# CREATE TABLE p_test(pid INT PRIMARY KEY);
CREATE TABLE
postgres=# CREATE TABLE c_test(cid INT,pid INT,CONSTRAINT fk_pid_c_test_p_test FOREIGN KEY (pid) REFERENCES p_test(pid));
CREATE TABLE

3、openGauss VARCHAR(n)变长字符串，n是指字节长度

虽然openGauss是基于PG开发的，但是如果从PG迁移到OpenGauss会比较坑，因为以前设计的字段长度在多字节的情况下可能都不够了，需要重新设计长度

--openGauss
postgres=# DROP TABLE IF EXISTS test;
DROP TABLE
postgres=# CREATE TABLE test(name VARCHAR(4));
CREATE TABLE
postgres=# INSERT INTO test VALUES('好a');
INSERT 0 1
postgres=# INSERT INTO test VALUES('好的');   
ERROR:  value too long for type character varying(4)
CONTEXT:  referenced column: name
postgres=# SELECT * FROM test;
 name 
------
 好a
(1 row)

postgres=# SELECT OCTET_LENGTH('好a'),LENGTH('好a'),OCTET_LENGTH('好的'),LENGTH('好的');
 octet_length | length | octet_length | length 
--------------+--------+--------------+--------
            4 |      2 |            6 |      2
(1 row)

--PostgreSQL10.3
postgres=# DROP TABLE IF EXISTS test;
DROP TABLE
postgres=# CREATE TABLE test(name VARCHAR(4));
CREATE TABLE
postgres=# INSERT INTO test VALUES('好a');
INSERT 0 1
postgres=# INSERT INTO test VALUES('好的');   
INSERT 0 1
postgres=# SELECT * FROM test;
 name 
------
 好a
 好的
(2 rows)

postgres=# SELECT OCTET_LENGTH('好a'),LENGTH('好a'),OCTET_LENGTH('好的'),LENGTH('好的');
 octet_length | length | octet_length | length 
--------------+--------+--------------+--------
            4 |      2 |            6 |      2
(1 row)

4、openGauss空字符串默认转换为NULL

这个对PG用户来说也比较坑，有的业务空字符串和NULL本身代表了不同的业务属性，现在全部变为同一个值了，需要修改业务，另外还有个约束的问题，多字段主键，有可能其中某一个字段没有值，可以使用空字符串代替（主键中的字段不允许为NULL），但是现在由于空字符串默认转换为NULL了，导致约束建不上去，解决办法是定义一个常量值来代替空字符串

--openGauss
postgres=# DROP TABLE IF EXISTS test;
DROP TABLE
postgres=# CREATE TABLE test(name VARCHAR(32) NOT NULL);
CREATE TABLE
postgres=# INSERT INTO test VALUES('');
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (null).
postgres=#  DROP TABLE IF EXISTS test;
DROP TABLE
postgres=# CREATE TABLE test(id VARCHAR(32) NOT NULL,pid VARCHAR(32) NOT NULL,CONSTRAINT pk_id_pid_test PRIMARY KEY(id,pid));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pk_id_pid_test" for table "test"
CREATE TABLE
postgres=# INSERT INTO test(id,pid) VALUES('A','B');
INSERT 0 1
postgres=# INSERT INTO test(id,pid) VALUES('A','');
ERROR:  null value in column "pid" violates not-null constraint
DETAIL:  Failing row contains (A, null).
postgres=# SELECT * FROM test;
 id | pid 
----+-----
 A  | B
(1 row)

--PostgreSQL10.3
postgres=# DROP TABLE IF EXISTS test;
DROP TABLE
postgres=# CREATE TABLE test(name VARCHAR(32) NOT NULL);
CREATE TABLE
postgres=# INSERT INTO test VALUES('');
INSERT 0 1
postgres=# SELECT * FROM test;
 name 
------
 
(1 row)

postgres=# DROP TABLE IF EXISTS test;
DROP TABLE
postgres=# CREATE TABLE test(id VARCHAR(32) NOT NULL,pid VARCHAR(32) NOT NULL,CONSTRAINT pk_id_pid_test PRIMARY KEY(id,pid));
CREATE TABLE
postgres=# INSERT INTO test(id,pid) VALUES('A','B');
INSERT 0 1
postgres=# INSERT INTO test(id,pid) VALUES('A','');
INSERT 0 1
postgres=# SELECT * FROM test;
 id | pid 
----+-----
 A  | B
 A  | 
(2 rows)

5、openGauss中不支持DATE转换函数

openGauss中不支持直接使用DATE类型转换函数，但是可以使用其它的方式代替

--openGauss
postgres=# SELECT DATE('2020-07-28');
ERROR:  syntax error at or near "("
LINE 1: SELECT DATE('2020-07-28');
                   ^
postgres=# SELECT '2020-07-28'::DATE;
      timestamp      
---------------------
 2020-07-28 00:00:00
(1 row)

postgres=# SELECT DATE '2020-07-28';
      timestamp      
---------------------
 2020-07-28 00:00:00
(1 row)

postgres=# SELECT CAST('2020-07-28' AS DATE);
      timestamp      
---------------------
 2020-07-28 00:00:00
(1 row)

--PostgreSQL10.3
postgres=# SELECT DATE('2020-07-28');
    date    
------------
 2020-07-28
(1 row)

postgres=# SELECT '2020-07-28'::DATE;
    date    
------------
 2020-07-28
(1 row)

postgres=# SELECT DATE '2020-07-28';
    date    
------------
 2020-07-28
(1 row)

postgres=# SELECT CAST('2020-07-28' AS DATE);
    date    
------------
 2020-07-28
(1 row)

6、openGauss默认对普通用户不开放public模式的权限

如果使用社区编译好的包安装，那么默认对普通用户不开放public模式的权限，如果是自己编译，在初始化gs_initdb时可以不加-S参数，这样就有public模式权限；如果不想编译，又想使用public模式怎么办？

--赋予相应权限
postgres=# CREATE ROLE testuser LOGIN PASSWORD "Test_2020" CREATEDB;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
postgres=# CREATE DATABASE testdb WITH OWNER=testuser ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='zh_CN.utf8' LC_CTYPE='zh_CN.utf8';
CREATE DATABASE
postgres=# \c testdb testuser
Password for user testuser: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "testuser".
testdb=> CREATE TABLE test(id INT);
ERROR:  permission denied for schema public
testdb=> \c testdb omm                        
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".
testdb=# GRANT ALL ON SCHEMA public TO testuser;
GRANT
testdb=# \c testdb testuser
Password for user testuser: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "testuser".
testdb=> CREATE TABLE test(id INT);
CREATE TABLE
testdb=> INSERT INTO test VALUES(1);
INSERT 0 1
testdb=> SELECT * FROM test;
 id 
----
  1
(1 row)

testdb=> \dnS+
                                  List of schemas
        Name        | Owner | Access privileges |           Description            
--------------------+-------+-------------------+----------------------------------
 cstore             | omm   |                   | reserved schema for DELTA tables
 dbe_perf           | omm   |                   | dbe_perf schema
 information_schema | omm   | omm=UC/omm       +| 
                    |       | =U/omm            | 
 pg_catalog         | omm   | omm=UC/omm       +| system catalog schema
                    |       | =U/omm            | 
 pg_toast           | omm   |                   | reserved schema for TOAST tables
 public             | omm   | omm=UC/omm       +| standard public schema
                    |       | =UC/omm          +| 
                    |       | testuser=UC/omm   | 
 snapshot           | omm   |                   | snapshot schema
(7 rows)

--或者直接把public OWNER给业务用户
testdb=> \c testdb omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".
testdb=#  ALTER SCHEMA public OWNER TO testuser;
ALTER SCHEMA
testdb=# \c testdb testuser
Password for user testuser: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "testuser".
testdb=> \dnS+
                                     List of schemas
        Name        |  Owner   |  Access privileges   |           Description            
--------------------+----------+----------------------+----------------------------------
 cstore             | omm      |                      | reserved schema for DELTA tables
 dbe_perf           | omm      |                      | dbe_perf schema
 information_schema | omm      | omm=UC/omm          +| 
                    |          | =U/omm               | 
 pg_catalog         | omm      | omm=UC/omm          +| system catalog schema
                    |          | =U/omm               | 
 pg_toast           | omm      |                      | reserved schema for TOAST tables
 public             | testuser | testuser=UC/testuser+| standard public schema
                    |          | =UC/testuser         | 
 snapshot           | omm      |                      | snapshot schema
(7 rows)

--或者直接sysadmin权限（不安全）
ALTER USER testuser SYSADMIN;

7、PostgreSQL9.2版本后新增的语法特性openGauss基本都不支持

比较常用的ON CONFLICT不支持，但是openGauss可以使用MERGE INTO来代替，这里使用了兼容ORACLE的语法；FILTER也不支持，可以使用CASE WHEN来代替；列、索引、序列都不支持IF NOT EXISTS语法，如果一定要可以自己写函数解决；序列不支持重命名，这个也可以自定义函数解决；另外还有物化视图、分区表原生继承语法、自定义聚合函数、逻辑流复制等都不支持。

postgresql opengauss

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者