Vertica Quick Tip: Empty String Vs. NULL

Posted May 30, 2018 by Jim Knicely, Big Data Solutions Architect, Vertica

Programmer
An empty string (”) is treated as a NULL value in Oracle, while 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) Have Fun!