Vertica Analytics Platform Version 10.1.x Documentation

SQL Data Types

The following table summarizes the internal data types that Vertica supports. It also shows the default placement of null values in projections. The Size column lists uncompressed bytes.

External tables also support the ROW, which cannot be used for Vertica-managed tables.

Data Type Size / bytes Description NULL Sorting

Binary

BINARY

1 to 65,000

Fixed-length binary string

NULLS LAST
VARBINARY

1 to 65,000

Variable-length binary string

NULLS LAST
LONG VARBINARY

1 to 32,000,000

Long variable-length binary string

NULLS LAST
BYTEA

Synonyms for VARBINARY

RAW

Boolean

BOOLEAN

1

True or False or NULL

NULLS LAST

Character / Long

CHAR

1 to 65,000

Fixed-length character string

NULLS LAST
VARCHAR

1 to 65,000

Variable-length character string

NULLS LAST
LONG VARCHAR

1 to 32,000,000

Long variable-length character string

NULLS LAST

Date/Time

DATE

8

Represents a month, day, and year

NULLS FIRST
TIME

8

Represents a time of day without timezone

NULLS FIRST
DATETIME Synonyms for TIMESTAMP
SMALLDATETIME
TIME WITH TIMEZONE

8

Represents a time of day with timezone

NULLS FIRST
TIMESTAMP

8

Represents a date and time without timezone

NULLS FIRST
TIMESTAMP WITH TIMEZONE

8

Represents a date and time with timezone

NULLS FIRST
INTERVAL

8

Measures the difference between two points in time

NULLS FIRST
INTERVAL DAY TO SECOND 8

Represents an interval measured in days and seconds

NULLS FIRST
INTERVAL YEAR TO MONTH 8

Represents an interval measured in years and months

NULLS FIRST

Approximate Numeric

DOUBLE PRECISION

8

Signed 64-bit IEEE floating point number, requiring 8 bytes of storage

NULLS LAST
FLOAT

8

Signed 64-bit IEEE floating point number, requiring 8 bytes of storage

NULLS LAST
FLOAT(n)

8

Signed 64-bit IEEE floating point number, requiring 8 bytes of storage

NULLS LAST
FLOAT8

8

Signed 64-bit IEEE floating point number, requiring 8 bytes of storage

NULLS LAST
REAL

8

Signed 64-bit IEEE floating point number, requiring 8 bytes of storage

NULLS LAST

Exact Numeric

INTEGER 8

Signed 64-bit integer, requiring 8 bytes of storage

NULLS FIRST
INT 8

Signed 64-bit integer, requiring 8 bytes of storage

NULLS FIRST
BIGINT 8

Signed 64-bit integer, requiring 8 bytes of storage

NULLS FIRST
INT8 8

Signed 64-bit integer, requiring 8 bytes of storage

NULLS FIRST
SMALLINT 8

Signed 64-bit integer, requiring 8 bytes of storage

NULLS FIRST
TINYINT 8

Signed 64-bit integer, requiring 8 bytes of storage

NULLS FIRST
DECIMAL 8+

8 bytes for the first 18 digits of precision, plus 8 bytes for each additional 19 digits

NULLS FIRST
NUMERIC 8+

8 bytes for the first 18 digits of precision, plus 8 bytes for each additional 19 digits

NULLS FIRST
NUMBER 8+

8 bytes for the first 18 digits of precision, plus 8 bytes for each additional 19 digits

NULLS FIRST
MONEY 8+

8 bytes for the first 18 digits of precision, plus 8 bytes for each additional 19 digits

NULLS FIRST
Spatial
GEOMETRY 1 to 10,000,000 Coordinates expressed as (x,y) pairs, defined in the Cartesian plane. NULLS LAST
GEOGRAPHY 1 to 10,000,000 Coordinates expressed in longitude/latitude angular values, measured in degrees
UUID
UUID 16 Stores universally unique identifiers (UUIDs). NULLS FIRST
Complex
ARRAY 1 to 32,000,000 Collection of values of a primitive type. Same as the primitive type
SET 1 to 32,000,000 Collection of unique values of a primitive type. Same as the primitive type