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

The following table lists the predefined data types.

Table 1-3 Predefined data types

ClassificationData type#1Data 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) [Figure] 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]38, 0 [Figure] n [Figure]38, 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 x 10-324 to [Figure]3.4 x 10308 #4
SMALLFLT or REALSingle-precision floating-point number (4 bytes)Single-precision floating-point number with a value of approximately [Figure]1.4 x 10-45 to [Figure]3.4 x 1038 #4
Character data#10CHAR[ACTER] [(n)][CHARACTER SET character-set-specification]Fixed-length character string (length: n bytes)
  • If no character set is specified:
    Fixed-length (n bytes) string of one-byte characters.
  • If a character set is specified:
    Fixed-length (n bytes) string of characters from that character set.
n is a positive integer such that 1 [Figure] n[Figure] 30,000. The default for n is 1.
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)
  • If no character set is specified:
    Variable-length (maximum length of n bytes) character string of one-byte characters.
  • If a character set is specified:
    Variable-length (maximum length of n bytes) string of characters from that character set.
n is a positive integer such that 1 [Figure] n[Figure] 32,000. The actual length is 0 or greater.
Attributes for the character data are specified by character-set-specification. For details, see 1.3 Character sets.
National character data#6, #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.
[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 [Figure] n [Figure]16,000. The real length is 0 or greater.
Mixed character data#6MCHAR [(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.
[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 [Figure] n[Figure] 32,000. The real length is 0 or greater.
Date dataDATEDate (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 dataTIMETime (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 dataTIMESTAMP[(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 dataINTERVAL YEAR TO DAYDate 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 dataINTERVAL HOUR TO SECONDTime 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 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 specified#5. 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 dataBOOLEAN#7Logical value (4 bytes)Permissible logical values are TRUE, FALSE, and UNKNOWN.
Note 1
The following shows the single-byte characters that are used for any whitespace characters when comparing or converting character data and mixed character data:
Data typeCharacter set specificationSpace character code
Character dataDefaultX'20'
EBCDIKX'40'
UTF16X'0020'#
Mixed character dataX'20'
National character dataDepends on the character set being used
#
To use data encoded in UTF-16 in the ? parameter, specify the character set name in the character set descriptor area.
Specifying UTF-16 data handling in the preprocessing options or embedded variable definitions allows data encoded in UTF-16 to also be used in embedded variables.
The possible values for the character set name are as follows:
  • UTF16
  • UTF-16BE
  • UTF-16LE
The space character codes used for the specifiable character set names are as follows:
  • UTF16 or UTF-16BE
    X'0020'
  • UTF-16LE
    X'2000'
For details about UTF-16LE and UTF-16BE (information on character sets that can be set in the character set descriptor area (SQLCSN)), see the HiRDB Version 9 UAP Development Guide.
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 lists, 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 x 1024
M1 [Figure] n [Figure]2048n x 1048576​
G1 [Figure] n [Figure]2n 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.

#10
  • If you specify a character encoding type other than sjis in the pdntenv command (pdsetup command in the UNIX edition), you cannot use character data for which EBCDIK is specified as the character-set-specification.
  • If you specify a character encoding type other than utf-8 in the pdntenv command (pdsetup command in the UNIX edition), you cannot use character data for which UTF16 is specified as the character-set-specification.
    When character-set-specification is set to UTF16, n must be specified as a multiple of 2.
#11: The range of ss is 00 to 61 (seconds) if you set the pd_leap_second operand to allow leap seconds to be specified. For details about the pd_leap_second operand, see the manual HiRDB Version 9 System Definition.

(2) User-defined type

The following table describes the 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.