1.2.1 Data types

Data types can be divided into the following two classes:

Format

data-type::={Predefined-data-type|user-defined-type}

Explanation
Organization of this subsection
(1) Predefined data type
(2) User-defined type

(1) Predefined data type

Table 1-3 shows the provided predefined data types.

Table 1-3 Predefined data types

ClassificationData type1Data formatDescription
Numeric dataINT[EGER]Integer (4-byte binary)Integer value in the range -2147483648​ to 2147483647​
SMALLINTInteger (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
[Figure](m+1)/2[Figure] 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 [Figure] m [Figure]29, 0 [Figure] n [Figure]29, n [Figure]m. The default for m is 15; the default for n is 0.
FLOAT or DOUBLE PRECISIONDouble-precision floating-point number (8 bytes)Double-precision floating-point number with a value of approximately [Figure]4.9 [Figure] 10-324 to [Figure]3.4 [Figure] 10308 4
SMALLFLT or REALSingle-precision floating-point number (4 bytes)Single-precision floating-point number with a value of approximately [Figure]1.4 [Figure] 10-45 to [Figure]3.4 [Figure] 1038 4
Character dataCHAR[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 [Figure] n [Figure]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 [Figure] n [Figure]32,000. The real length is 0 or greater.
National character data6, 9NCHAR[(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 [Figure] n [Figure]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 [Figure] n [Figure]16,000. The real length is 0 or greater.
Mixed character data6MCHAR [(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 [Figure] n[Figure] 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 [Figure] n[Figure] 32,000. The real length is 0 or greater.
Date dataDATEDate (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 dataTIMETime (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 dataTIMESTAMP[(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 dataINTERVAL YEAR TO DAYDate 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 dataINTERVAL HOUR TO SECONDTime 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 dataBLOB [(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 dataBINARY(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 [Figure] n[Figure] 2,147,483,647 bytes.
Logical dataBOOLEAN7Logical 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.

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.

UnitAllowable range of nActual maximum length (bytes)
K1 [Figure] n [Figure]2097152​n [Figure]1024
M1 [Figure] n [Figure]2048n [Figure]1048576​
G1 [Figure] n [Figure]2n [Figure]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 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).

(2) User-defined type

Table 1-4 shows the available user-defined type.

Table 1-4 User-defined type

Data typeData formatExplanation
Abstract data typeNot applicableData type defined by CREATE TYPE. Attribute definitions and routines can be defined in the data type.