Return All Cast Failures as NULL
Jim_Knicely
- Select Field - Administrator
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)
Have fun!
0