Data Type Coercion Operators (CAST)

Data type coercion (casting) passes an expression value to an input conversion routine for a specified data type, resulting in a constant of the indicated type.

Syntax

SELECT CAST ( expression AS data_type ) 

SELECT expression::data_type 

SELECT data_type 'string'

Parameters

expression

data_type

Notes

• In Vertica, data type coercion (casting) can be invoked by an explicit cast request. It must use one of the following constructs:

  => SELECT CAST ( expression AS data_type ) 

  => SELECT expression::data_type 

  => SELECT data_type 'string'
  

• The explicit type cast can be omitted if there is no ambiguity as to the type the constant must be. For example, when a constant is assigned directly to a column, it is automatically coerced to the column's data type.

• If a binary value is cast (implicitly or explicitly) to a binary type with a smaller length, the value is silently truncated. For example:

  => SELECT 'abcd'::BINARY(2); 
  ?column?
  ----------
   ab
  (1 row)
  

• Similarly, if a character value is cast (implicitly or explicitly) to a character value with a smaller length, the value is silently truncated. For example:

  => SELECT 'abcd'::CHAR(3); 
  ?column?
  ----------
   abc
  

• Vertica supports only casts and resize operations as follows:

  • BINARY to and from VARBINARY
  • VARBINARY to and from LONG VARBINARY
  • BINARY to and from LONG VARBINARY

• On binary data that contains a value with fewer bytes than the target column, values are right-extended with the zero byte '\0' to the full width of the column. Trailing zeros on variable-length binary values are not right-extended:

  => SELECT 'ab'::BINARY(4), 'ab'::VARBINARY(4), 'ab'::LONG VARBINARY(4);  
    ?column?  | ?column? | ?column?
  ------------+----------+----------
   ab\000\000 | ab       | ab
  (1 row)
  

Examples

=> SELECT CAST((2 + 2) AS VARCHAR);
 ?column?
----------
 4
(1 row)

=> SELECT (2 + 2)::VARCHAR;
 ?column?
----------
 4
(1 row)

=> SELECT INTEGER '123';
 ?column?
----------
      123
(1 row)

=> SELECT (2 + 2)::LONG VARCHAR
 ?column?
----------
 4
(1 row)

=> SELECT '2.2' + 2;
   ERROR:  invalid input syntax for integer: "2.2"

=> SELECT FLOAT '2.2' + 2;
 ?column?
----------
      4.2
(1 row)