Day: August 13, 2019

Oracle treats empty string as NULL

SQL> create table test ( col1 varchar(10), col2 varchar(10) );

Table created.

SQL> insert into test values ( '',null);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test where col1 is null;

COUNT(*)
----------
1

SQL> select count(*) from test where col2 is null;

COUNT(*)
----------
1

PostgreSQL  does not treat empty string as NULL

This could bring some code issue when migrating Oracle database to PostgreSQL.

elms=> create table test ( col1 varchar(10), col2 varchar(10) );
CREATE TABLE

testdb=> insert into test values ( '',null);
INSERT 0 1

testdb=> select count(*) from test where col1 is null;
count
-------
0
(1 row)

testdb=> select count(*) from test where col2 is null;
count
-------
1
(1 row)