Learn: List of Netezza Data Types and Best Practices

1) Netezza Data Types

Below is an example of the data types available in Netezza at this time. Here's a Netezza CREATE TABLE example demonstrating all the supported Netezza data types.

                CREATE TABLE NETEZZA_TABLE_NAME (
                    BYTEINT_COLUMN BYTEINT,
                    SMALLINT_COLUMN SMALLINT,
                    INTEGER_COLUMN INTEGER,
                    BIGINT_COLUMN BIGINT,
                    BOOLEAN_COLUMN BOOLEAN,
                    CHAR_COLUMN CHARACTER(2),
                    VARCHAR_COLUMN CHARACTER VARYING(10),
                    NCHAR_COLUMN NATIONAL CHARACTER(10),
                    DATE_COLUMN DATE,
                    TIME_COLUMN TIME,
                    TIME_WITH_TIME_ZONE_COLUMN TIME WITH TIME ZONE,
                    TIMESTAMP_COLUMN TIMESTAMP,
                    INTERVAL_COLUMN INTERVAL,
                    DOUBLE_PRECISION_COLUMN DOUBLE PRECISION,
                    FLOAT_COLUMN DOUBLE PRECISION,
                    NUMERIC_COLUMN NUMERIC(18,0),
                    NVARCHAR_COLUMN NATIONAL CHARACTER VARYING(10),
                    REAL_COLUMN REAL,
                    ST_GEOMETRY_COLUMN ST_GEOMETRY(10),
                    VARBINARY_COLUMN BINARY VARYING(10),
                    DECIMAL_COLUMN DECIMAL(16,2),
                    FLOAT_COLUMN FLOAT(6),
                    MONEY_COLUMN MONEY
                ) DISTRIBUTE ON (BIGINT_COLUMN) |[ DISTRIBUTE ON RANDOM] ;

2) Netezza Data Types Best Practices

Convert INTEGER types for better performance and zonemap utilization.
Use floating point data types (REAL/DOUBLE PRECISION) sparingly as they are lossy by nature, affecting collocated joins and performance.
Ensure consistent data types for the same column on different tables to optimize performance.
Use Char(x) instead of Varchar(x) when expecting fixed-length data as this helps save disk space and improves performance due to reduced I/O.

3) Restrictions: Netezza Database Maximum Lengths

Columns per table or view: 1600 (4 distributions per table)
Connections to the server: 2000 (default connection value 500)
Character fields: 64,000 characters
Database and column names: 128 bytes
Row size: 65,535 bytes (applies to query results as well)

Understanding Netezza Data Types

Character Data Types

CHAR(n): Fixed-length character string with length 'n'. If the input is smaller than 'n', it will be padded with spaces.

VARCHAR(n): Variable-length character string with maximum length 'n'.

NCHAR(n): Fixed-length Unicode character string with length 'n'. If the input is smaller than 'n', it will be padded with spaces.

NVARCHAR(n): Variable-length Unicode character string with maximum length 'n'.

Numeric Data Types

INT: 4-byte signed integer, ranging from -2147483648 to 2147483647.

BIGINT: 8-byte signed integer, ranging from -9223372036854775808 to 9223372036854775807.

SMALLINT: 2-byte signed integer, ranging from -32768 to 32767.

FLOAT(p), FLOAT(p,s): Single precision floating point number. 'p' is the total number of digits after the decimal point, and 's' (optional) is the maximum number of digits before the decimal point.

DOUBLE PRECISION, DOUBLE(p), DOUBLE(p,s): Double precision floating point number. 'p' and 's' have the same meaning as for FLOAT.

Date and Time Data Types

DATE: Date data type, with format YYYY-MM-DD.

TIME: Time of day data type, with format HH:MI:SS.ssss.

DATETIME, TIMESTAMP: Combined date and time data type, with format YYYY-MM-DD HH:MI:SS.ssss.

Best Practices for Using Netezza Data Types

Use the smallest data type possible: Smaller data types require less storage space and lead to faster query performance.

Avoid null values: Null values can cause issues with indexes, aggregations, and join operations. Use default values or check constraints whenever possible to prevent nulls.

Use TIMESTAMP for all date/time data: TIMESTAMP is more efficient than DATE or TIME when performing date arithmetic or sorting.

Consider using CHAR(1) for flag-type columns: This reduces storage space and can improve query performance compared to BOOLEAN or BIT data types.

Learn: List of Netezza Data Types and Best Practices

1) Netezza Data Types

2) Netezza Data Types Best Practices

3) Restrictions: Netezza Database Maximum Lengths

Learn: List of Netezza Data Types and Best Practices

Understanding Netezza Data Types

Character Data Types

Numeric Data Types

Date and Time Data Types

Best Practices for Using Netezza Data Types

Illustration: Example Usage of Data Types in Netezza