Vertica and ODBC data type translation

Most data types are transparently converted between Vertica and ODBC.

Most data types are transparently converted between Vertica and ODBC. This section explains several data types require special handling.

Vertica Data Types C Data Type ODBC C Typedef C Type Identifier
BINARY, VARBINARY char[] SQL_BINARY SQL_C_BINARY
LONG VARBINARY char[] SQL_LONGVARBINARY SQL_C_BINARY
BOOLEAN SQLSMALLINT SQL_SMALLINT SQL_C_SSHORT
CHAR, VARCHAR char[] SQL_CHAR SQL_C_CHAR
LONG VARCHAR char[] SQL_LONGVARCHAR SQL_C_CHAR
DATE SQL_DATE_STRUCT SQL_TYPE_DATE SQL_C_TYPE_DATE
TIME SQL_TIME_STRUCT SQL_TYPE_TIME SQL_C_TYPE_TIME
TIMESTAMP SQL_TIMESTAMP_STRUCT SQL_TYPE_TIMESTAMP SQL_C_TYPE_TIMESTAMP
INTERVAL SQL_INTERVAL_STRUCT SQL_INTERVAL_DAY_TO_SECOND SQL_C_INTERVAL_DAY_TO_SECOND
INTERVAL DAY TO SECOND SQL_INTERVAL_STRUCT SQL_INTERVAL_DAY_TO_SECOND SQL_C_INTERVAL_DAY_TO_SECOND
INTERVAL YEAR TO MONTH SQL_INTERVAL_STRUCT SQL_INTERVAL_YEAR_TO_MONTH SQL_C_INTERVAL_YEAR_TO_MONTH
DOUBLE PRECISION FLOAT SQLREAL SQL_REAL SQL_C_FLOAT
INTEGER, BIGINT, SMALLINT SQLBIGINT SQL_BIGINT SQL_C_SBIGINT
NUMERIC, DECIMAL, NUMBER, MONEY SQL_NUMERIC_STRUCT SQL_NUMERIC SQL_C_NUMERIC
GEOMETRY char[] SQL_LONGVARBINARY SQL_C_CHAR
GEOGRAPHY char[] SQL_LONGVARBINARY SQL_C_CHAR
UUID SQLGUID (see note below) SQL_GUID SQL_C_GUID

Notes

  • The GEOMETRY and GEOGRAPHY data types are treated as LONG VARCHAR data by the ODBC driver.

  • Vertica supports the standard interval data types supported by ODBC. See Interval Data Types in Microsoft's ODBC reference.

  • Vertica version 9.0.0 introduced the UUID data type, including JDBC support for UUIDs. The Vertica ADO.NET, ODBC, and OLE DB clients added full support for UUIDs in version 9.0.1. Vertica maintains backwards compatibility with older supported client driver versions that do not support the UUID data type, as follows:

    When an older client... Vertica...
    Queries tables with UUID columns Translates the native UUID values to CHAR values.
    Inserts data into a UUID column Converts the CHAR value sent by the client into a native UUID value.
    Queries a UUID column's metadata Reports its data type as CHAR.

See also