Long Data Types
Store data up to 32,000,000 bytes:
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.
The maximum size for the LONG
data types is 32,000,000 bytes. Use the LONG
data types only when you need to store data greater than 65,000 bytes, which is the maximum size for VARBINARY
and VARCHAR
data types. Such data might include unstructured data, online comments or posts, or small log files.
Default length is 1048576.
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 |
Specifies the length of the byte string (column width, declared in bytes (octets)), in CREATE TABLE statements). Maximum value: 32,000,000. |
octet_length |
Specifies the length of the string (column width, declared in bytes (octets)), in CREATE TABLE statements). Maximum value: 32,000,000. |
Notes
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 of LONG data types, but not all the operations that VARCHAR and VARBINARY take. - Use the
VARBINARY
andVARCHAR
data types, instead of theirLONG
counterparts, whenever possible. TheVARBINARY
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.');