Cast Failures

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.

When a cast fails, the result returned depends on the data type.

Enabling Strict Time Casts

You can enable all cast failures to result in an error, including those for Date/Time data types. Doing so allows you to see the reason why some or all of the cast failed. To return an error instead of NULL, use the ALTER SESSION statement with the SET parameter:

ALTER SESSION SET EnableStrictTimeCasts=1;

The following example shows a Date/Time cast failure that returns NULL:

=> CREATE TABLE mytable (a VARCHAR);
CREATE TABLE
=> INSERT INTO mytable VALUES('string');
OUTPUT 
--------
1
(1 row)
=> INSERT INTO mytable VALUES('1');
OUTPUT 
--------
1
(1 row)
=> SELECT a::time FROM mytable;
a 
---
 
 
(2 rows)

When you specify EnableStrictTimeCasts, the cast failure returns an error:

=> ALTER SESSION SET EnableStrictTimeCasts=1;
ALTER SESSION
=> SELECT a::time FROM mytable;
ERROR 2005:  Invalid input syntax for time: "1"

Returning All Cast Failures as NULL

To explicitly cast an expression to a requested data type, use the following construct:

SELECT expression::data_type

Using this command to cast any values to a conflicting data type returns the following error:

ERROR 2827:  Could not convert "string" from column table.a to an int8

In addition to the :: cast, Vertica supports the use of ::!. Use ::! instead of ::, if you want to return:

• NULL instead of an error for any non-Date/Time data types
• NULL instead of an error after setting EnableStrictTimeCasts

Returning all cast failures as NULL allows those expressions that succeeded during the cast to appear in the result. Those expressions which failed during the cast, however, have a NULL value.

The following example shows a cast failure that returns an error:

=> CREATE TABLE mytable (a VARCHAR);
CREATE TABLE
=> INSERT INTO mytable VALUES('string');
OUTPUT 
--------
1
(1 row)
=> INSERT INTO mytable VALUES('1');
OUTPUT 
--------
1
(1 row)
=> SELECT a::int FROM mytable;
ERROR 2827:  Could not convert "string" from column mytable.a to an int8

When you use ::!, the cast fails for the "string" value and returns NULL. However, it succeeds for the "1" value and returns 1:

=> SELECT a::!int FROM mytable;
a 
---
  
1
(2 rows)