| Type | SIZE | DESCRIPTION |
| BOOL | 1 | boolean, ‘true’/’false’ |
| CHAR | 1 | single character |
| INT8 | 8 | ~18 digit integer, 8-byte storage |
| INT2 | 2 | -32 thousand to 32 thousand, 2-byte storage |
| INT4 | 4 | -2 billion to 2 billion integer, 4-byte storage |
| FLOAT4 | 4 | single-precision floating point number, 4-byte storage |
| FLOAT8 | 8 | double-precision floating point number, 8-byte storage |
| DOUBLE | 15 | same as a FLOAT15, 15-bytes of storge, 32-bit |
| BPCHAR | VAR | char(length), blank-padded string, fixed storage length |
| VARCHAR | VAR | varchar(length), non-blank-padded string, variable storage length |
| DATE | 4 | ANSI SQL date |
| TIME | 8 | hh:mm:ss, ANSI SQL time |
| TIMESTAMP | 8 | date and time |
| INTERVAL | 12 | @ , time interval |
| TIMETZ | 12 | hh:mm:ss, ANSI SQL time |
| NUMERIC | 19 | numeric(precision, decimal), arbitrary precision number |
| INT1 | 1 | -128 to 127, 1-byte storage |
| NCHAR | VAR | nchar |
| NVARCHAR | VAR | nvarchar |
| ST_GEOMETRY | VAR | st_geometry |
| VARBINARY | VAR | varbinary |
Data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL. However, different database offers the different data types for columns. Netezza data types are almost similar to what the traditional RDBMS supports.
When you issue Netezza create table command each column in a database tables has to have name and a data type associated with it. The data type is based on the types of data which are stored inside the each column of the table. When you perform Netezza alter table to add or change the column that also requires the Netezza data type associated with it. You can get information about the column name and data types when you use Netezza describe table along with table name.
Netezza Data Types
Below are the list of an example of the data types available in Netezza at this time. Here is the Netezza CREATE TABLE example having all the supported Netezza data types at this time.
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] ;
Netezza Data Types Best Practices
- INTEGER types provide better performance and zonemap utilization so convert NUMERIC types with scale 0 to INTEGER types
- Floating point data types (REAL/DOUBLE PRECISION) are, by definition, lossy in nature those eliminates possibility for collocated joins and that will definitely affect the performance. Use them only if you working with very large numbers or very small fractions
- Inconsistent data types for the same column on different tables affects performance so always use the same data types for same columns on different tables.
- Use Char(x) instead of Varchar(x) when you expect the data to be a fixed length as this not only helps to save disk space but also helps performance due to reduced I/O.
Restrictions: Netezza Database Maximum Lengths
- Columns: Maximum per table or view – 1600 (maximum 4 distributions per table)
- Connections: Maximum connections to the server – 2000. Default connection value 500
- Character: Maximum number of characters in a char/varchar field – 64,000. You will not be able to load file to table which exceeds 64,000 characters
- Names: maximum length of database and column names – 128 bytes
- Row size: Maximum row size – 65,535 bytes. Limit also applies to the result set of a query. Within each row, there is a small amount of overhead for special columns and other factors such as padding.