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
|
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 |
||
RAW
|
|||
BOOLEAN
|
1 |
True or False or NULL |
NULLS LAST
|
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
|
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
|
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
|
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 |