Binary data types
Netezza supports two types of binary data types for use in tables and external tables.
| Type | Value |
|---|---|
| VARBINARY(n) | Variable length field from 1 up to 64,000 bytes. Use the VARBINARY type to store binary data in a type-specific field and apply restricts or other processing against the columns as needed. |
| ST_GEOMETRY(n) | Variable length field from 1 up to 64,000 bytes. The ST_GEOMETRY data type holds geometric binary information. The ST_ GEOMETRY type is useful for storing geometric data as processed by spatial analysis functions and other geometric analysis to identify columns with binary geometry-oriented data. |
While it is possible to store binary data in a VARCHAR column, it is difficult to differentiate between VARCHAR data and binary data in the database and for processing and restricts.
Columns that use the binary data types do not support some of the common query processing operations. For example, binary data type columns cannot be used in ordering, grouping, or magnitude comparisons, or in aggregates such as sum(), avg(), distinct(), or min/max/median comparisons. The binary data cannot be implicitly or explicitly cast to other types.
You can insert the data for the binary objects using tools such as user-defined functions (UDFs) that create the binary data, or you can specify the content using hexadecimal string literal notation. The hexadecimal string literal representation has the following format:
x'hexValue'
A valid hexadecimal string literal must begin with the letter x in uppercase or lowercase followed by a single-quoted string of hexadecimal characters. Each hexadecimal character is formed as a pair of two characters from the numbers 0 through 9 and the letters A through F (uppercase or lowercase). For example, the string 'hello' appears as x'68656c6c6f' in hexadecimal string format. A sample INSERT statement with a hexadecimal string literal follows:
insert into my_table values (1, x'68656c6c6f');
If you do not specify the x prefix, enclose the string in single quotation marks, or if the string contains an odd number of characters, the hexadecimal string literal is invalid and the system returns an error.