Data types can be divided into the following two classes:
data-type::={Predefined-data-type|user-defined-type}
Table 1-3 shows the provided predefined data types.
Table 1-3 Predefined data types
Classification | Data type1 | Data format | Description |
---|---|---|---|
Numeric data | INT[EGER] | Integer (4-byte binary) | Integer value in the range -2147483648 to 2147483647 |
SMALLINT | Integer (2-byte binary) | Integer value in the range -32768 to 32767 | |
[LARGE]DEC[IMAL] [(m[,n])] or NUMERIC [(m[,n])] | Fixed-point number (packed decimal with![]() ![]() | Fixed-point number whose precision (total number of digits) is m and whose decimal scaling position (number of digits following the decimal point) is n, where m and n are positive integers such that 1 ![]() ![]() ![]() ![]() ![]() | |
FLOAT or DOUBLE PRECISION | Double-precision floating-point number (8 bytes) | Double-precision floating-point number with a value of approximately ![]() ![]() ![]() ![]() | |
SMALLFLT or REAL | Single-precision floating-point number (4 bytes) | Single-precision floating-point number with a value of approximately ![]() ![]() ![]() ![]() | |
Character data | CHAR[ACTER] [(n)] | Fixed-length character string (length: n bytes) | Fixed-length character string of one-byte characters with a length of n bytes, where n is a positive integer such that 1 ![]() ![]() |
VARCHAR(n) or CHAR[ACTER] VARYING(n) | Variable-length character string (maximum length: n bytes) | Variable-length character string of one-byte characters with a maximum length of n bytes, where n is a positive integer such that 1 ![]() ![]() | |
National character data6, 9 | NCHAR[(n)] or NATIONAL CHAR[ACTER] [(n)] | Fixed-length national character string (length: n characters) | Fixed-length national character string of two-byte characters with a length of n characters (2n bytes), where n is a positive integer such that 1 ![]() ![]() |
NVARCHAR(n) or NATIONAL CHAR[ACTER] VARYING(n) or NCHAR VARYING(n) | Variable-length national character string (maximum length: n characters) | Variable-length national character string of two-byte characters with a maximum length of n character (2n bytes), where n is a positive integer such that 1 ![]() ![]() | |
Mixed character data6 | MCHAR [(n)] | Fixed length, mixed character string (length: n bytes) | This is a fixed length, mixed character string with a length of n bytes containing both one-byte characters and two-byte characters, where n is a positive integer, 1 ![]() ![]() |
MVARCHAR(n) | Variable length, mixed character string (maximum length: n bytes) | This is a variable length, mixed character string with a maximum length of n bytes containing both one-byte characters and two-byte characters, where n is a positive integer, 1 ![]() ![]() | |
Date data | DATE | Date (4-byte, unsigned, packed format, YYYYMMDD) YYYY: 0001-9999 (year) MM: 01-12 (month) DD: 01-last day of the month (day) | Date represented by the year, month, and day. |
Time data | TIME | Time (3-byte, unsigned, packed format, hhmmss) hh: 00-23 (hour) mm: 00-59 (minute) ss: 00-59 (second) | Time represented by the hour, minute, and second. |
Time stamp data | TIMESTAMP[(p)] | Time stamp (unsigned packed format 7 to 10 bytes long, YYYYMMDDhhmmss [nn...n]) YYYY: 0001-9999 (year) MM: 01-12 (month) DD: 01-the last day of a given month (day) hh: 00-23 (hour) mm: 00-59 (minute) ss: 00-59 (second) nn...n: fractional seconds in p digits (n: 0-9) | This is the time stamp data type having six areas: year, month, day, hour, minute, and second. p is an integer such that p=0, 2, 4, or 6. The default is p=0. |
Date interval data | INTERVAL YEAR TO DAY | Date interval (5-byte, packed format, 0YYYYMMDDs) YYYY: 0000-9999 (number of years) MM: 00-99 (number of months)2 DD: 00-99 (number of days) s: Sign (positive: C, F; negative: D)8 | Interval between two dates, in the range -9999 years, 11 months, 99 days to 9999 years, 11 months, 99 days. |
Time interval data | INTERVAL HOUR TO SECOND | Time interval (4-byte, packed format, 0hhmmsst) hh: 00-99 (hours) mm: 00-99 (minutes)3 ss: 00-99 (seconds)3 t: Sign (positive: C, F; negative: D)8 | Interval between two times, in the range -99 hours, 59 minutes, 59 seconds to 99 hours, 59 minutes, 59 seconds. |
Large object data | BLOB [(n[{K|M|G}])] or BINARY LARGE OBJECT [(n[{K|M|G}])] | Binary data string (maximum length: n bytes) K: kilobytes M: megabytes G: gigabytes | This is a binary data string with a maximum length of n bytes. The default for n is 2,147,483,647 bytes. The real length is 0 or greater. Units K, M, and G can be specified5. If a unit (K, M, G) is omitted, the length is assumed to be in bytes by default. |
Binary data | BINARY(n) | Binary data string (maximum length: n bytes) | This is a binary data string with a maximum length of n bytes, where n is required. The actual length is 0 or greater. n is a positive integer such that 1 ![]() ![]() |
Logical data | BOOLEAN7 | Logical value (4 bytes) | Permissible logical values are TRUE, FALSE, and UNKNOWN. |
1 If there are multiple data types for a data format, the typical data type is used thereafter in this manual as a representative example.
2 When the number 12 or greater is used, the year is incremented by 1.
3 When the number 60 or greater is specified in mm or ss, the hour or minute, respectively, is incremented by 1.
4 The allowable range of a floating-point number is limited by the data representation available on the hardware used to execute the SQL.
5 The following table shows, for each unit of length, the allowable range of values and the maximum length.
Unit | Allowable range of n | Actual maximum length (bytes) |
---|---|---|
K | 1 ![]() ![]() | n ![]() |
M | 1 ![]() ![]() | n ![]() |
G | 1 ![]() ![]() | n ![]() |
If the calculated value of an actual maximum length is 2147483648, the value is reduced to 2147483647.
6 When lang-c is specified in the pdsetup command as the character codes type, national character data and mixed character data cannot be defined (UNIX version only).
7 BOOLEAN can be used only as the data type for a function that provides a return value; it cannot be used as the data type for a column, SQL variable, or SQL parameter.
8 For details on the sign part of the decimal, date interval, and time interval types, see 1.2.4 Notes on using the decimal type.
9 National character data cannot be defined if the character code type utf-8 is specified in the pdntenv command (pdsetup command in the UNIX version).
Table 1-4 shows the available user-defined type.
Table 1-4 User-defined type
Data type | Data format | Explanation |
---|---|---|
Abstract data type | Not applicable | Data type defined by CREATE TYPE. Attribute definitions and routines can be defined in the data type. |