postgresql与Oracle：空字符串与null

作者：瀚高PG实验室 （Highgo PG Lab）- 丹心明月 

空字符串：两个单引号，中间无空格等任何内容

在postgresql中，空字符串与null是不同的；而oracle中，空字符串与null等同。测试如下：

postgresql中：
postgres=# insert into testnull values(1,'feikong');
INSERT 0 1
postgres=# insert into testnull values(2,null);
INSERT 0 1
postgres=# insert into testnull values(3,'');
INSERT 0 1
postgres=#  select * from testnull;
 id |  name   
----+---------
  1 | feikong
  2 |
  3 |
(3 rows)

postgres=# select * from testnull where name is null;     ---只有2为null
 id | name
----+------
  2 |
(1 row)

postgres=# select * from testnull where name is  not null;
 id |  name   
----+---------
  1 | feikong
  3 |
(2 rows)


Oracle中：
SQL> insert into testnull values(1,'feikong');

1 row inserted

SQL> insert into testnull values(2,null);

1 row inserted

SQL> insert into testnull values(3,'');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from testnull;

        ID NAME
---------- ----------
         1 feikong
         2
         3

SQL> select * from testnull where name is null;   --2,3为null

        ID NAME
---------- ----------
         2
         3

SQL> select * from testnull where name is  not null;

        ID NAME
---------- ----------
         1 feikong