PostgreSQL oracle 相容性 - 字元串内嵌 NULL字元（空字元）chr(0) 轉換為 chr(32)

标簽

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

https://github.com/digoal/blog/blob/master/201807/20180713_01.md#%E8%83%8C%E6%99%AF 背景

在Oracle中，存儲字元串時，允許使用者将空字元存到字元串中，雖然這種用法可能不常見，但是給Oracle遷移到PG的使用者帶來了一些小麻煩，因為PG中chr(0)是作為結束符來處理的，不允許作為使用者輸入傳入字元串中。

如果要存儲chr(0)字元，PostgreSQL 必須存儲在位元組流類型中。

https://github.com/digoal/blog/blob/master/201807/20180713_01.md#oracle%E4%BE%8B%E5%AD%90 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  
           

https://github.com/digoal/blog/blob/master/201807/20180713_01.md#postgresql 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")));  
           

https://github.com/digoal/blog/blob/master/201807/20180713_01.md#%E5%85%BC%E5%AE%B9%E6%80%A7 相容性

當有文本資料需要從Oracle導出到PG時，如果裡面存儲了空字元，可以先做一下轉換(比如轉換為空格)，解決不相容問題。

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