Data Type Coercion

Vertica supports two types of data type casting:

• Implicit casting: The expression automatically converts the data from one type to another.
• Explicit casting: An SQL statement specifies the target data type for the conversion.

Implicit Casting

The ANSI SQL-92 standard supports implicit casting among similar data types:

• Number types
• CHAR, VARCHAR, LONG VARCHAR
• BINARY, VARBINARY, LONG VARBINARY

Vertica supports two types of nonstandard implicit casts:

• From CHAR to FLOAT, to match the one from VARCHAR to FLOAT. The following example converts the CHAR '3' to a FLOAT so it can add the number 4.33 to the FLOAT result of the second expression:

  => SELECT '3'::CHAR + 4.33::FLOAT;
   ?column? 
  ----------
     7.33
  (1 row)
  

• Between DATE and TIMESTAMP. The following example DATE to a TIMESTAMP and calculates the time 6 hours, 6 minutes, and 6 seconds back from 12:00 AM:

  => SELECT DATE('now') - INTERVAL '6:6:6';
        ?column?       
  ---------------------
   2013-07-30 17:53:54
  (1 row)
  

When there is no ambiguity about the data type of an expression value, it is implicitly coerced to match the expected data type. In the following command, the quoted string constant '2' is implicitly coerced into an INTEGER value so that it can be the operand of an arithmetic operator (addition):

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

A concatenate operation explicitly takes arguments of any data type. In the following example, the concatenate operation implicitly coerces the arithmetic expression 2 + 2 and the INTEGER constant 2 to VARCHAR values so that they can be concatenated.

=> SELECT 2 + 2 || 2;
 ?column?
----------
 42
(1 row)

Another example is to first get today's date:

=> SELECT DATE 'now';
  ?column?
------------
 2013-07-31
(1 row)

The following command converts DATE to a TIMESTAMP and adds a day and a half to the results by using INTERVAL:

=> SELECT DATE 'now' + INTERVAL '1 12:00:00';
      ?column?
---------------------
 2013-07-31 12:00:00
(1 row)

Most implicit casts stay within their relational family and go in one direction, from less detailed to more detailed. For example:

• DATE to TIMESTAMP/TZ
• INTEGER to NUMERIC to FLOAT
• CHAR to FLOAT
• CHAR to VARCHAR
• CHAR and/or VARCHAR to FLOAT
• CHAR to LONG VARCHAR
• VARCHAR to LONG VARCHAR
• BINARY to VARBINARY
• BINARY to LONG VARBINARY
• VARBINARY to LONG VARBINARY

More specifically, data type coercion works in this manner in Vertica:

INT8 

FLOAT8

FLOAT8 

INT8

VARCHAR 

CHAR

VARBINARY

BINARY

VARCHAR

LONG VARCHAR

VARBINARY

LONG VARBINARY

No other types cast to or from LONGVARBINARY, VARBINARY, or BINARY. In the following list, <any> means one these types: INT8, FLOAT8, DATE, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, INTERVAL.

• <any> -> VARCHAR—implicit
• VARCHAR -> <any>—explicit, except that VARCHAR->FLOAT is implicit
• <any> <-> CHAR—explicit
• DATE -> TIMESTAMP/TZ—implicit
• TIMESTAMP/TZ -> DATE—explicit, loses time-of-day
• TIME -> TIMETZ—implicit, adds local timezone
• TIMETZ -> TIME—explicit, loses timezone
• TIME -> INTERVAL—implicit, day to second with days=0
• INTERVAL -> TIME—explicit, truncates non-time parts
• TIMESTAMP <-> TIMESTAMPTZ—implicit, adjusts to local timezone
• TIMESTAMP/TZ -> TIME—explicit, truncates non-time parts
• TIMESTAMPTZ -> TIMETZ—explicit
• VARBINARY -> LONG VARBINARY—implicit
• LONG VARBINARY -> VARBINARY—explicit
• VARCHAR -> LONG VARCHAR—implicit

• LONG VARCHAR -> VARCHAR—explicit

  Important: Implicit casts from INTEGER, FLOAT, and NUMERIC to VARCHAR are not supported. If you need that functionality, write an explicit cast:

  CAST(x AS data-type-name) 

  or

  x::data-type-name

  The following example casts a FLOAT to an INTEGER:

  => SELECT(FLOAT '123.5')::INT;
   ?column?
  ----------
        124
  (1 row)
  

String-to-numeric data type conversions accept formats of quoted constants for scientific notation, binary scaling, hexadecimal, and combinations of numeric-type literals:

• Scientific notation:

  => SELECT FLOAT '1e10';  
    ?column?
  -------------
   10000000000
  (1 row)
  

• BINARY scaling:

  => SELECT NUMERIC '1p10'; 
   ?column?
  ----------
       1024
  (1 row)
  

• hexadecimal:

  => SELECT NUMERIC '0x0abc'; 
   ?column?
  ----------
       2748
  (1 row)
  

Examples

The following example casts three strings as NUMERICs:

=> SELECT NUMERIC '12.3e3', '12.3p10'::NUMERIC, CAST('0x12.3p-10e3' AS NUMERIC);
 ?column? | ?column? |     ?column?
----------+----------+-------------------
    12300 |  12595.2 | 17.76123046875000
(1 row)

This example casts a VARBINARY string into a LONG VARBINARY data type:

=> SELECT B'101111000'::LONG VARBINARY;
 ?column?
----------
 \001x
(1 row)

The following example concatenates a CHAR with a LONG VARCHAR, resulting in a LONG VARCHAR:

=> \set s ''''`cat longfile.txt`''''
=> SELECT length ('a' || :s ::LONG VARCHAR);
 length
----------
 65002
(1 row)

The following example casts a combination of NUMERIC and INTEGER data into a NUMERIC result:

=> SELECT (18. + 3./16)/1024*1000;
                ?column?
-----------------------------------------
 17.761230468750000000000000000000000000
(1 row)