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 (m+1)/2 bytes)8	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 m 29, 0 n 29, n m. The default for m is 15; the default for n is 0.
	FLOAT or DOUBLE PRECISION	Double-precision floating-point number (8 bytes)	Double-precision floating-point number with a value of approximately 4.9 10-324 to 3.4 10308 4
	SMALLFLT or REAL	Single-precision floating-point number (4 bytes)	Single-precision floating-point number with a value of approximately 1.4 10-45 to 3.4 1038 4
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 n 30,000. The default for n is 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 n 32,000. The real length is 0 or greater.
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 n 15,000. The default for n is 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 n 16,000. The real length is 0 or greater.
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 n 30,000. The default for n is 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 n 32,000. The real length is 0 or greater.
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 n 2,147,483,647 bytes.
Logical data	BOOLEAN7	Logical value (4 bytes)	Permissible logical values are TRUE, FALSE, and UNKNOWN.

Note 1

When comparing character data with mixed character data or converting data, the system uses one-byte character spaces (X'20') as filler characters. When comparing national character data or converting data, the system uses two-byte spaces as filler characters. The code for the two-byte space varies with the type of character code that is used.

Note 2

See the Permissible characters in SQL column in Table 1-1 for the character codes with the available one-byte and two-byte characters.

¹ If there are multiple data types for a data format, the typical data type is used thereafter in this manual as a representative example.

² When the number 12 or greater is used, the year is incremented by 1.

³ When the number 60 or greater is specified in mm or ss, the hour or minute, respectively, is incremented by 1.

⁴ The allowable range of a floating-point number is limited by the data representation available on the hardware used to execute the SQL.

⁵ 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 2097152	n 1024
M	1 n 2048	n 1048576
G	1 n 2	n 1073741824

If the calculated value of an actual maximum length is 2147483648, the value is reduced to 2147483647.

⁶ 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).

⁷ 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.

⁸ 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.

⁹ 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.