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: A 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 of scalar types:

  • 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 statement, 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:

Type Direction Type Notes
INT8 

>

FLOAT8

Implicit, can lose significance

FLOAT8 

>

INT8

Explicit, rounds

VARCHAR 

<->

CHAR

Implicit, adjusts trailing spaces

VARBINARY

<->

BINARY

Implicit, adjusts trailing NULs

VARCHAR

>

LONG VARCHAR

Implicit, adjusts trailing spaces

VARBINARY

>

LONG VARBINARY

Implicit, adjusts trailing NULs

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

    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)
    

Collections

Collections (arrays and sets) of scalar types can be cast implicitly and explicitly. Casting a collection casts each element of the collection. You can, for example, cast an ARRAY[VARCHAR] to an ARRAY[INT] or a SET[DATE] to SET[TIMESTAMPTZ]. You can cast between arrays and sets.

Casting can increase the storage needed for a column. For example, if you cast an array of INT to an array of VARCHAR(50), each element takes more space and thus the array takes more space. If the difference is extreme or the array has many elements, this could mean that the array no longer fits within the space allotted for the column. In this case the operation reports an error and fails.

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)

In SQL expressions, pure numbers between (–2^63–1) and (2^63–1) are INTEGERs. Numbers with decimal points are NUMERIC.