Data type coercion

Vertica supports two types of data type casting:.

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:

Conversion 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

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)
    

Complex types

Collections (arrays and sets) 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.

When casting to a bounded native array, inputs that are too long are truncated. When casting to a non-native array (an array containing complex data types including other arrays), if the new bounds are too small for the data the cast fails

Rows (structs) can be cast implicitly and explicitly. Casting a ROW casts each field value. You can specify new field names in the cast or specify only the field types to use the existing field names.

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)

See also