Return All Cast Failures as NULL

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited November 2018 in Tips from the Team

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!


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;
(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:

Have fun!

Sign In or Register to comment.