MogDB/openGauss 与 postgresql 日常使用差异

原创高云龙云和恩墨技术服务团队 2020-10-30

2762

密码加密

pg默认密码加密方式是md5
og默认密码加密方式是sha256

使用navicate、pgadmin3等客户端开发工具访问og，需要修改加密方式

如果在本地用用户名密码登陆数据库没问题
但是用其他工具连接数据库报用户或密码错误
可能是密码加密方式不对，需要看pg_hba.conf 及参数

password_encryption_type = 0		#Password storage type, 0 is md5 for PG, 1 is sha256 + md5, 2 is sha256 only

字符串存储

在pg里，char(n)、varchar(n) n代表是字符；最多存储1GB
在og里，char(n)、varcahr(n) n代表的是字节，nvarchar2(n) n代表是字符；最多存储10MB

---
---pg字符测试
---
postgres=# \d dt
                       Table "public.dt"
 Column |         Type         | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
 id     | integer              |           |          |
 col1   | character varying(8) |           |          |

postgres=# insert into dt values(3,'中文字符长度测试');
INSERT 0 1
postgres=# insert into dt values(4,'yingwen8');
INSERT 0 1
postgres=# insert into dt values(4,'yingwen88');
ERROR:  value too long for type character varying(8)

---
---og字符测试
---
mydb=# \d+ dt
                                 Table "public.dt"
 Column |         Type         | Modifiers | Storage  | Stats target | Description
--------+----------------------+-----------+----------+--------------+-------------
 id     | integer              |           | plain    |              |
 col1   | character varying(8) |           | extended |              |
 col2   | nvarchar2(8)         |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

mydb=# insert into dt(id,col1) values(3,'yingwen8');
INSERT 0 1
mydb=# insert into dt(id,col1) values(3,'yingwen88');
ERROR:  value too long for type character varying(8)
CONTEXT:  referenced column: col1
mydb=# insert into dt(id,col1) values(3,'中文测试');
ERROR:  value too long for type character varying(8)
CONTEXT:  referenced column: col1
mydb=# insert into dt(id,col1) values(3,'中文测');
ERROR:  value too long for type character varying(8)
CONTEXT:  referenced column: col1

mydb=# insert into dt(id,col2) values(4,'中文字符长度测试');
INSERT 0 1
mydb=# insert into dt(id,col2) values(4,'yingwen8');
INSERT 0 1
mydb=# insert into dt(id,col2) values(4,'yingwen88');
ERROR:  value too long for type nvarchar2(8)
CONTEXT:  referenced column: col2
mydb=#

null 与空字符

在pg里 null 与空是不相等的
在og里新建数据库默认是兼容oracle模式，所以’’ 转换成 null，没有’’

---
---pg测试
---
postgres=# create table dt(id int,col1 varchar(8));
CREATE TABLE
postgres=# insert into dt values(1,null);
INSERT 0 1
postgres=# insert into dt values(2,'');
INSERT 0 1
postgres=# select * from dt;
 id | col1
----+------
  1 |
  2 |
(2 rows)

postgres=# select * from dt where col1 is null;
 id | col1
----+------
  1 |
(1 row)

postgres=# select * from dt where col1='';
 id | col1
----+------
  2 |
(1 row)

如果保持 null 与空分开，可以在创建数据库时兼容其他模式

CREATE DATABASE database_name
    [ [ WITH ] {[ OWNER [=] user_name ]|
           [ TEMPLATE [=] template ]|
           [ ENCODING [=] encoding ]|
           [ LC_COLLATE [=] lc_collate ]|
           [ LC_CTYPE [=] lc_ctype ]|
           [ DBCOMPATIBILITY [=] compatibility_type ]|
           [ TABLESPACE [=] tablespace_name ]|
           [ CONNECTION LIMIT [=] connlimit ]}[...] ];

DBCOMPATIBILITY [ = ] compatibilty_type
指定兼容的数据库的类型。
取值范围：A、B、C、PG。分别表示兼容O、MY、TD和POSTGRES。

兼容mysql