Return All Cast Failures as NULL: Quick Tip

Posted November 12, 2018 by Jim Knicely, Vertica Principal Solution Architect

When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you attempt to cast conflicting conversions, such as trying to convert a varchar expression that contains letters to an integer.

However, using the nifty cast ::! instead of cast :: returns ALL cast failures as NULL instead of generating an error!

Example: dbadmin=> \d test List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key -------+-------+--------+-------------+------+---------+----------+-------------+ public | test | c | varchar(80) | 80 | | f | f | (1 row) dbadmin=> SELECT c dbadmin-> FROM test; c --- 1 A (2 rows) dbadmin=> SELECT c, c::INT "Will Error" dbadmin-> FROM test; ERROR 2827: Could not convert "A" from column test.c to an int8 dbadmin=> SELECT c, c::!INT "Will NOT Error" dbadmin-> FROM test; c | Will NOT Error --+---------------- 1 | 1 A | (2 rows) Helpful Link:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/CastFails.htm

Have fun!