Empty String Vs. NULL

[Deleted User]

Jim Knicely authored this tip.

An empty string ('') is treated as a NULL value in Oracle, but in Vertica, an empty string is not treated as a NULL value.

So when using Vertica, if you want to indicate that a column value is unknown, be sure to use NULL and not an empty string!

Example:

In Oracle:

SQL> SELECT CASE WHEN '' IS NULL THEN 'Yup' ELSE 'Nope' END AS "Are they the same?" FROM dual;

Are they the same?
------------------
Yup

SQL> SELECT LENGTH(NULL), LENGTH('') FROM dual;

LENGTH(NULL) LENGTH('')
------------ ----------


SQL> CREATE TABLE store_nulls (my_nulls VARCHAR(10));

Table created.

SQL> INSERT INTO store_nulls SELECT '' FROM dual;

1 row created.

SQL> SELECT COUNT(*) FROM store_nulls WHERE my_nulls IS NULL;

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

In Vertica:

dbadmin=> SELECT CASE WHEN '' IS NULL THEN 'Yup' ELSE 'Nope' END AS "Are they the same?" FROM dual;
Are they the same?
--------------------
Nope
(1 row)

dbadmin=> SELECT LENGTH(NULL), LENGTH('');
LENGTH | LENGTH
--------+--------
        |      0
(1 row)

dbadmin=> CREATE TABLE store_nulls (my_nulls VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT INTO store_nulls SELECT '';
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT COUNT(*) FROM store_nulls WHERE my_nulls IS NULL;
COUNT
-------
     0
(1 row)