Vertica Analytics Platform Version 10.1.x Documentation

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

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
Default value: 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.

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