PostgreSQL Oracle兼容性之 - '' 空字符

标签

PostgreSQL , Oracle , 兼容性 , ''空字符 , 隐式NULL转换

---

背景

Oracle 对''有一些特殊的处理，默认会转成NULL。使得''可以适合任意的数据类型。

然而对于PostgreSQL来说，没有做这层转换，所以''并不能输入给任意类型。

Oracle

SQL> create table a(id int, c1 varchar2(10) default '', c2 date default '');  
  
Table created.  
  
SQL> insert into a (id) values(1);  
  
1 row created.  
  
SQL> select * from a where c1 is null;  
  
        ID C1         C2  
---------- ---------- ---------  
         1  
  
SQL> select * from a where c2 is null;  
  
        ID C1         C2  
---------- ---------- ---------  
         1  

然而实际上这样查询却查不到结果，是不是很让人惊讶：

SQL> select * from a where c1 = '';  
  
no rows selected  

default ''就是说默认值为NULL。

ORACLE内部把''转换成了NULL。(不仅时间类型，字符串ORACLE也会这么干，所以语义上很混乱，实际上个人认为是ORACLE的一个不严谨的地方)

PostgreSQL

PG不做这个转换，所以非字符串类型，使用''都会报错。

postgres=# select ''::timestamp;  
ERROR:  invalid input syntax for type timestamp: ""  
LINE 1: select ''::timestamp;  
               ^  

为了兼容Oracle，建议用户改''为直接输入NULL，语义上也通畅。

postgres=# create table a(id int, c1 varchar(10) default null, c2 timestamp(0) default null);  
CREATE TABLE  
postgres=# insert into a (id) values (1);  
INSERT 0 1  
postgres=# select * from a where c1 is null;  
 id | c1 | c2   
----+----+----  
  1 |    |   
(1 row)  
  
postgres=# select * from a where c2 is null;  
 id | c1 | c2   
----+----+----  
  1 |    |   
(1 row)  
  
postgres=# select * from a where c1 = '';  
 id | c1 | c2   
----+----+----  
(0 rows)