SQL Data Types
The following table summarizes the data types that Vertica supports. It also shows the default placement of null values in projections. The Size column is listed as uncompressed bytes.
| Type | Size (bytes) | Description | NULL Sorting |
|---|---|---|---|
|
Binary types (For more information, see Binary Data Types.) |
|||
BINARY |
1 to 65000 |
Fixed-length binary string |
NULLS LAST |
VARBINARY |
1 to 65000 |
Variable-length binary string |
NULLS LAST |
LONG VARBINARY |
1 to 32,000,000 |
Long variable-length binary string |
NULLS LAST |
BYTEA |
1 to 65000 |
Variable-length binary string (synonym for VARBINARY) |
NULLS LAST |
RAW |
1 to 65000 |
Variable-length binary string (synonym for VARBINARY) |
NULLS LAST |
|
Boolean types (See Boolean Data Type.) |
|||
BOOLEAN |
1 |
True or False or NULL |
NULLS LAST |
|
Character types (See Character Data Types and Long Data Types.) |
|||
CHAR |
1 to 65000 |
Fixed-length character string |
NULLS LAST |
VARCHAR |
1 to 65000 |
Variable-length character string |
NULLS LAST |
LONG VARCHAR |
1 to 32,000,000 |
Long variable-length character string |
NULLS LAST |
|
Date/time types (See Date/Time Data Types.) |
|||
DATE |
8 |
Represents a month, day, and year |
NULLS FIRST |
DATETIME |
8 |
Represents a date and time with or without timezone (synonym for |
NULLS FIRST |
SMALLDATETIME |
8 |
Represents a date and time with or without timezone (synonym for |
NULLS FIRST |
TIME |
8 |
Represents a time of day without timezone |
NULLS FIRST |
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 types (See DOUBLE PRECISION (FLOAT).) |
|||
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 types (See Numeric Data Types.) |
|||
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 |
