
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!