Empty String Vs. NULL

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners
edited May 30 in Vertica Tips

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)
Sign In or Register to comment.