Return All Cast Failures as NULL

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited November 12 in Vertica Tips

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/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/CastFails.htm

Have fun!

Sign In or Register to comment.