Data types can be divided into the following two classes:
data-type::={Predefined-data-type|user-defined-type}
The following table lists the predefined data types.
Table 1-3 Predefined data types
Classification | Data type#1 | 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#10 | CHAR[ACTER] [(n)][CHARACTER SET character-set-specification] | Fixed-length character string (length: n bytes) |
![]() ![]() Attributes for the character data are specified by character-set-specification. For details, see 1.3 Character sets. |
[LONG] VARCHAR(n)[CHARACTER SET character-set-specification] or CHAR[ACTER] VARYING(n)[CHARACTER SET character-set-specification] | Variable-length character string (maximum length: n bytes) |
![]() ![]() Attributes for the character data are specified by character-set-specification. For details, see 1.3 Character sets. | |
National character data#6, #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 ![]() ![]() |
[LONG] 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 data#6 | 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 ![]() ![]() |
[LONG] 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 to 9999 (year) MM: 01 to 12 (month) DD: 01 to the last day of the specified month of the specified year (day) | Date represented by the year, month, and day. |
Time data | TIME | Time (3-byte, unsigned, packed format, hhmmss) hh: 00 to 23 (hour) mm: 00 to 59 (minute) ss: 00 to 59 (second)#11 | 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 to 9999 (year) MM: 01 to 12 (month) DD: 01 to the last day of the specified month of the specified year (day) hh: 00 to 23 (hour) mm: 00 to 59 (minute) ss: 00 to 59 (second)#11 nn...n: fractional seconds in p digits (n: 0 to 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 to 9999 (number of years) MM: 00 to 99 (number of months)#2 DD: 00 to 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 to 99 (hours) mm: 00 to 99 (minutes)#3 ss: 00 to 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 specified#5. 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 | BOOLEAN#7 | Logical value (4 bytes) | Permissible logical values are TRUE, FALSE, and UNKNOWN. |
Data type | Character set specification | Space character code |
---|---|---|
Character data | Default | X'20' |
EBCDIK | X'40' | |
UTF16 | X'0020'# | |
Mixed character data | X'20' | |
National character data | Depends on the character set being used |
#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 lists, 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 x 1024 |
M | 1 ![]() ![]() | n x 1048576 |
G | 1 ![]() ![]() | n x 1073741824 |
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 edition 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 about the sign part of the decimal, date interval, and time interval types, see 1.2.4 Notes on using the decimal type.
#9: If you specify utf-8 or chinese-gb18030 as the character encoding type in the pdntenv command (pdsetup command in the UNIX edition), national character data cannot be defined.
The following table describes the 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. |