Exact numeric data types
You use numeric data types to store numbers. The following table describes the integer numeric types in various ranges. The larger the range, the more storage it requires.
Choose integer data
types for distribution and join columns. Whenever possible use integer
data types to benefit from the added performance of zone maps.
| Type | Value | Disk usage |
|---|---|---|
| byteint (alias int1) | 8-bit values in range -128 to 127 | 1 byte |
| smallint (alias int2) | 16-bit values in range -32,768 to 32,767 | 2 bytes |
| integer (alias int and int4) | 32-bit values in range -2,147,483,648 to 2,147,483,647 | 4 bytes |
| bigint (alias int8) | 64-bit values in range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 8 bytes |
You use fixed-point numeric data types to define the
numeric rounding to a specific decimal place. The following table
describes the fixed-point numeric data types.
| Type | Value | Disk usage |
|---|---|---|
| numeric(p, s) | Numeric with precision p and scale s. Precision can range from 1 to 38, scale from 0 to the precision. | Precision of 9 or less (4 bytes) Precision of 10 - 18 (8 bytes) Precision of 19 - 38 (16 bytes) |
| numeric(p) | Equivalent to numeric(p, 0). | |
| numeric | Equivalent to numeric(18, 0). | 8 bytes |
| decimal | Although decimal is sometimes a distinct SQL data type, IBM® Netezza® SQL treats it as an alias for numeric. | 4 bytes to 16 bytes |
Always use the smallest integer or fixed-point numeric data type whenever possible. When you are converting source data to the Netezza system, you might need to analyze the data to determine the smallest data type that you can use.
To determine the smallest data size you
can use for integer and fixed-point numerics, type the following SQL
command:
SELECT MIN(column_name), MAX(column_name) FROM table_name;