Long Data Types
Store data up to 32000000 octets. Vertica supports two long data types:
LONG VARBINARY
: Variable-length raw-byte data, such as spatial data.LONG VARBINARY
values are not extended to the full width of the column.LONG VARCHAR
: Variable-length strings, such as log files and unstructured data.LONG VARCHAR
values are not extended to the full width of the column.
Use LONG
data types only when you need to store data greater than the maximum size of VARBINARY
and VARCHAR
data types (65 KB). Long data can include unstructured data, online comments or posts, or small log files.
Flex tables have a default LONG VARBINARY __raw__
column, with a NOT NULL
constraint. For more information, see Using Flex Tables.
Syntax
LONG VARBINARY [(max‑length)]
LONG VARCHAR [(octet‑length)]
Parameters
max‑length |
Optionally specifies the length of the byte string or column width, declared in bytes (octets). Maximum value: 32000000 Default value: 1 MB |
octet‑length |
Optionally specifies the length of the string or column width, declared in bytes (octets). Maximum value: 32000000 |
Optimized Performance
For optimal performance of LONG
data types, Vertica recommends that you:
- Use the
LONG
data types as storage only containers; Vertica supports operations on the content ofLONG
data types, but does not support all the operations thatVARCHAR
andVARBINARY
take. - Use
VARBINARY
andVARCHAR
data types, instead of theirLONG
counterparts, whenever possible.VARBINARY
andVARCHAR
data types are more flexible and have a wider range of operations. - Do not sort, segment, or partition projections on
LONG
data type columns. - Do not add constraints, such as a primary key, to any
LONG VARBINARY
orLONG VARCHAR
columns. - Do not join or aggregate any
LONG
data type columns.
Example
The following example creates a table user_comments
with a LONG VARCHAR
column and inserts data into it:
=> CREATE TABLE user_comments (id INTEGER, username VARCHAR(200), time_posted TIMESTAMP, comment_text LONG VARCHAR(200000)); => INSERT INTO user_comments VALUES (1, 'User1', TIMESTAMP '2013-06-25 12:47:32.62', 'The weather tomorrow will be cold and rainy and then on the day after, the sun will come and the temperature will rise dramatically.');