Long data types

Store data up to 32000000 octets.

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 Flex tables.

Syntax

LONG VARBINARY [(max-length)]
LONG VARCHAR [(octet-length)]

Parameters

max-length
Length of the byte string or column width, declared in bytes (octets), up to 32000000.

Default: 1 MB

octet-length
Length of the string or column width, declared in bytes (octets), up to 32000000.

Default: 1 MB

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 of LONG data types, but does not support all the operations that VARCHAR and VARBINARY take.

  • Use VARBINARY and VARCHAR data types, instead of their LONG counterparts, whenever possible. VARBINARY and VARCHAR 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 or LONG VARCHAR columns.

  • Do not join or aggregate any LONG data type columns.

Examples

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.');