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:

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
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)

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)

Note

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