PostgreSQL oracle 兼容性 - 字符串内嵌 NULL字符（空字符）chr(0) 转换为 chr(32)

作者

digoal

日期

2018-07-13

标签

PostgreSQL , Oracle , chr(0) , 空字符 , 结束符 , 字符串

背景

在Oracle中，存储字符串时，允许用户将空字符存到字符串中，虽然这种用法可能不常见，但是给Oracle迁移到PG的用户带来了一些小麻烦，因为PG中chr(0)是作为结束符来处理的，不允许作为用户输入传入字符串中。

如果要存储chr(0)字符，PostgreSQL 必须存储在字节流类型中。

Oracle例子

1、chr(0)存入字符串中。

```
SQL> select 1 from dual where 'a'||chr(32)||'b' = 'a b';

     1

     1

SQL> select 1 from dual where 'a'||chr(0)||'b' = 'a b';

no rows selected

SQL> select 1 from dual where cast('a'||chr(0)||'b' as varchar2(10)) = 'a b';

no rows selected
```

2、将chr(0)转换为空格，即chr(32)

```
SQL> select replace ('a'||chr(0)||'b', chr(0), chr(32)) from dual;

REP

a b
```

转换后，判断是否相等

```
SQL> select 1 from dual where replace ('a'||chr(0)||'b', chr(0), chr(32)) = 'a b';

     1

     1

```

PostgreSQL

postgres=# select 'a'||chr(0)||'b'; ERROR: 54000: null character not permitted LOCATION: chr, oracle_compat.c:1000

src/backend/utils/adt/oracle_compat.c

```

993                 /*  
994                  * Error out on arguments that make no sense or that we can't validly  
995                  * represent in the encoding.  
996                  */  
997                 if (cvalue == 0)  
998                         ereport(ERROR,  
999                                         (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),

1000 errmsg("null character not permitted")));
```

兼容性

当有文本数据需要从Oracle导出到PG时，如果里面存储了空字符，可以先做一下转换(比如转换为空格)，解决不兼容问题。

```
SQL> select replace ('a'||chr(0)||'b', chr(0), chr(32)) from dual;

REP

a b
```

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.