Data types and aliases
The following table lists the supported data types by their preferred name form, and includes supported aliases and some notes about the values. For more information about the data types and values, see the IBM Netezza Database User’s Guide.
| Data type | Alias names | Notes |
|---|---|---|
| BOOLEAN | BOOL | A boolean field can store true values, false values, and null.
You can use the following words to specify booleans:
|
| CHAR | CHARACTER, CHAR(n), CHARACTER(n) | Fixed-length character string, blank padded to length n. If you do not specify n, the default is an unsized CHAR value. The maximum character string size is 64,000. |
| VARCHAR | CHARACTER VARYING, VARCHAR(n), CHARACTER VARYING(n), CHAR VARYING(n) | Variable length character string to a maximum length of n. If you do not specify n, the default is an unsized VARCHAR value. There is no blank padding, and the value is stored as entered. The maximum character string size is 64,000. |
| NCHAR | NATIONAL CHARACTER, NATIONAL CHAR(n), NCHAR(size) | Fixed-length character string, blank padded to length n. If you do not specify n, the default is an unsized NCHAR value. The maximum length is 16,000 characters. |
| NVARCHAR | NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n), and NVARCHAR(n) | Variable length character string to a maximum length of n. If you do not specify n, the default is an unsized NVARCHAR value. The maximum length is 16,000 characters. |
| DATE | Specifies a day with resolution that spans January 1, 0001 to December 31, 9999 (centered around 2000-01-01). | |
| TIMESTAMP | DATETIME | Has a date part and a time part, with seconds stored to 6 decimal
positions. The value represents the number of microseconds since midnight
2000-01-01. Min: -63,082,281,600,000,000 (00:00:00, 1/1/0001) Max: 252,455,615,999,999,999 (23:59:59.999999, 12/31/9999) |
| TIME | TIME WITHOUT TIME ZONE | Hours, minutes, and seconds to 6 decimal positions, ranging from 00:00:00.000000 to 23:59:59.999999. This value is microsecond resolution that represents the time of day only (midnight to one microsecond before midnight). |
| INTERVAL | TIMESPAN | An interval of time. It has microsecond resolution and ranges from +/- 178000000 years. The time part represents everything but months and years (microseconds) and the month part represents months and years. For more information, see the section on IBM® Netezza® interval support in the IBM Netezza Database User’s Guide. |
| TIME WITH TIME ZONE | TIMETZ | Hours, minutes, seconds to 6 decimal positions, and time zone offset from GMT. Ranging from 00:00:00.000000+13:00 to 23:59:59.999999-12:59. |
| NUMERIC(p,s) | NUMERIC, DEC, DEC(p,s), DECIMAL(p,s), DECIMAL | Fixed-point numeric types with precision p and
scale s. Precision can range from 1 to 38, scale
from 0 to the precision. NUMERIC(p) is equivalent to NUMERIC(p,0). NUMERIC is an unsized numeric value. Although decimal is sometimes a distinct SQL data type, Netezza SQL treats it as an alias for NUMERIC. |
| REAL | FLOAT(p), FLOAT4 | Floating point number with precision p.
Precision values of 1 to 6 are equivalent to FLOAT(6), and are stored
as a 4-byte value. Netezza SQL prefers the type name real, as float(p) is considered more of an alias for the preferred form. |
| DOUBLE PRECISION | DOUBLE, FLOAT, FLOAT(p), FLOAT8 | Floating point number with precision p, from 7 to 14. Precision values 7 -
14 are equivalent to 14 and are stored as an 8-byte value. Netezza SQL prefers the type name double precision, as float(p) is considered more of an alias for the preferred form. |
| INTEGER | INT, INT4 | 32-bit values in range –2,147,483,648 to 2,147,483,647 |
| BYTEINT | INT1 | 8-bit values in the range –128 to 127 |
| SMALLINT | INT2 | 16-bit values in range –32,768 to 32,767 |
| BIGINT | INT8 | 64-bit values in range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |