The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Empty String Vs. NULL

edited May 2018 in Tips from the Team

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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.