Empty String and NULL in Oracle and PostgreSQL

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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.