6.2.1 List of data types

(1) Numeric data

This section describes numeric data.

To Page Top

(2) Character string data

■ CHARACTER

• This data type handles fixed-length character strings.

• The following shows the format to use when specifying this data type:

  CHAR, CHAR(n), CHARACTER, or CHARACTER(n)

• The length of the character string (number of bytes) is specified in n, where n is an integer in the range 1 to 32,000. If n is omitted, 1 is assumed.

• Literals are written in the form 'char'. For details about literals, see 6.3 Literals.

• Both half-width and full-width characters can be handled.

• When you perform comparisons on character string data, the ordering of the character encoding determines the ordering of the data being compared.

■VARCHAR, STRING

• This data type handles variable-length character strings.

• The following shows the format to use when specifying this data type:

  • In the case of VARCHAR

    VARCHAR(n)

  • In the case of STRING

    STRING

  Important

  • STRING type column can only be defined in a foreign table. You cannot define a STRING type column in a base table.

  • If STRING type is specified as the data type, HADB assumes that VARCHAR(32000000) is specified for the data type.

• In the preceding format, n specifies (in bytes) the maximum length of each character string. The value of n must be an integer in the range from 1 to 64,000. n cannot be omitted.

• The data format of the VARCHAR type is shown in the following figure.

  Figure 6‒6: Data format of VARCHAR type

  The character string data length (L) is represented by four bytes.

• Both half-width and full-width characters can be handled. The length of the character string can be 0 bytes.

• When you perform comparisons on character string data, the ordering of the character encoding determines the ordering of the data being compared.

• You cannot specify VARCHAR-type data whose length exceeds 32,000 bytes in the following locations:

  • Data type specified in the column definition in an ALTER TABLE statement

  • Data type specified in the column definition in a CREATE FOREIGN TABLE statement

  • Data type specified in the column definition in a CREATE TABLE statement

  • Data type specified in a table function column list

  • Post-conversion data type specified in the scalar function CAST

  • Post-conversion data type specified in the scalar function CONVERT

• STRING type cannot be specified in the following locations.

  • Data type specified in the column definition in an ALTER TABLE statement

  • Data type specified in the column definition in a CREATE TABLE statement

  • Data type specified in a table function column list

To Page Top

(3) Datetime data

■ DATE

• This is the data type for dates, with fields for the year, month, and day.

• The following shows the format to use when specifying this data type:

  DATE

• It can handle dates with a range of values from January 1, 0001 to December 31, 9999.

• The data length is 4 bytes. The data that is entered must be this length.

• The data format of the DATE type is shown in the following figure.

  Figure 6‒7: Data format of DATE type

• Literals are written in the form DATE'2012-03-30' or DATE'2012/03/30'. For details about literals, see 6.3 Literals.

■ TIME

• This is the data type for time, with fields for the hour, minute, and seconds.

• The following shows the format to use when specifying this data type:

  TIME(p) or TIME

  p specifies the fractional seconds precision (the number of digits to the right of the decimal point). You can specify a value of 0, 3, 6, 9, or 12 for p.

  If TIME is specified, p is assumed to be 0.

• This format can handle times with a range of values from 0 hours, 0 minutes, and 0.000000000000 seconds to 23 hours, 59 minutes, and 59.999999999999 seconds.

• The data length is 3 + ↑p ÷ 2↑ bytes. The data that is entered must be this length.

• The data format of the TIME type is shown in the following figure.

  Figure 6‒8: Data format of TIME type

  One digit is represented in 4 bits. If the fractional seconds precision is an odd number, zeros are stored in the final 4 bits.

• Literals are written in the form TIME'11:03:58.123456'. For details about literals, see 6.3 Literals.

■ TIMESTAMP (TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE)

• This is the data type for time stamps, with fields for the year, month, day, hour, minute, and seconds.

• There are two data types for the TIMESTAMP type:

  • TIMESTAMP WITHOUT TIME ZONE (time stamp data without time zone)

  • TIMESTAMP WITH TIME ZONE (time stamp data with time zone)

• The following shows the format to use when specifying this data type:

  • For TIMESTAMP WITHOUT TIME ZONE

    Specify in either of the following formats:

    TIMESTAMP(p)
    TIMESTAMP

  • For TIMESTAMP WITH TIME ZONE

    Specify in either of the following formats:

    TIMESTAMP(p) WITH TIME ZONE
    TIMESTAMP WITH TIME ZONE

    Values entered in TIMESTAMP WITH TIME ZONE (time stamp data with time zone) are converted to Coordinated Universal Time (UTC) and treated as UTC (+00:00) data.

    Example: 2025-10-21 15:00+09:00 is converted to 2025-10-21 06:00+00:00.

  p specifies the fractional seconds precision (the number of digits to the right of the decimal point). You can specify a value of 0, 3, 6, 9, or 12 for p.

  If (p) is omitted, p is assumed to be 0.

• It can handle time stamps with a range of values from January 1, 0001 0:0:0.000000000000 to December 31, 9999 23:59:59.999999999999.

• The data length is 7 + ↑p ÷ 2↑ bytes. The data that is entered must be this length.

• The data format of the TIMESTAMP type is shown in the following figure.

  Figure 6‒9: Data format of TIMESTAMP type

  One digit is represented in 4 bits. If the fractional seconds precision is an odd number, zeros are stored in the final 4 bits.

  Note

  TIMESTAMP WITH TIME ZONE (time stamp data with time zone) is converted to TIMESTAMP WITHOUT TIME ZONE (time stamp data without time zone) (converted to UTC); therefore, both time stamp data with time zone and time stamp data without time zone have the same data format.

• Literals are written in the following format. For details about literals, see 6.3 Literals.

  • For TIMESTAMP WITHOUT TIME ZONE

    TIMESTAMP'2025-07-30 11:03:58'

  • For TIMESTAMP WITH TIME ZONE

    TIMESTAMP'2025-07-30 11:03:58+09:00'
    TIMESTAMP'2025-07-30 11:03:58Z'

To Page Top

(4) Binary data

■ BINARY

• This is the data type for handling fixed-length binary data.

• The following shows the format to use when specifying this data type:

  BINARY(n), or BINARY

• The length of the binary data (number of bytes) is specified in n. If n is omitted, 1 is assumed.

• Literals are written in the form X'0A38ef92'. For details about literals, see 6.3 Literals.

• The data format of the BINARY type is shown in the following figure.

  Figure 6‒10: Data format of BINARY type

■ VARBINARY

• This is the data type for handling variable-length binary data.

• The following shows the format to use when specifying this data type:

  VARBINARY(n)

• In the preceding format, n specifies (in bytes) the maximum length of the binary data. The value of n must be an integer in the range from 1 to 32,000. n cannot be omitted.

• Literals are written in the form X'0A38ef92'. For details about literals, see 6.3 Literals.

• The length of the binary data can be 0 bytes.

• The data format of the VARBINARY type is shown in the following figure.

  Figure 6‒11: Data format of VARBINARY type

  The length of the binary data (L) is represented in two bytes.

To Page Top

(5) Logical data

■ BOOLEAN

• A data type that represents a logical value that can be true, false, or unknown.

• Unknown logical data is considered a null value.

• The following shows the format to use when specifying this data type:

  BOOLEAN

• Data length is 1 byte. It is treated as 'T'(0x54) for true, and 'F'(0x46) for false.

• Literals are specified as TRUE or FALSE. For details about literals, see 6.3 Literals.

To Page Top

(6) UUID data

■ UUID

• A data type that represents UUID.

• The following shows the format to use when specifying this data type:

  UUID

• The data is fixed-length binary data of 16 bytes.

• A literal is described in the format UUID'd68fab5b-f44c-42ae-8b8d-8da66ab31042'. For details about literals, see 6.3 Literals.

• The following figure shows the data format of the UUID type.

  Figure 6‒12: Data format of UUID type

  Explanation:

  One digit is represented in 4 bits.

  For UUID data in the format AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE, the following values are stored:

  A: 0x0 to 0x9, 0xA to 0xF

  B: 0x0 to 0x9, 0xA to 0xF

  C: 0x0 to 0x9, 0xA to 0xF

  D: 0x0 to 0x9, 0xA to 0xF

  E: 0x0 to 0x9, 0xA to 0xF

• The version of UUID handled by HADB depends on the application or system that generated the UUID data. HADB does not perform processing that is aware of the UUID version.

To Page Top

(7) Array data

■ ARRAY (array type)

• An array type is a data type that handles one-dimensional array data consisting of an ordered sequence of data elements.

• The following shows the format to use when specifying this data type:

  element-data-type ARRAY[maximum-number-of-elements]

  element-data-type:

  Specifies the array element data type.

  Specify numeric data, character string data, datetime data, binary data, logical data, UUID data, or structure data for the element data type. The description format of element data types follows the rules for the description format of each data type.

  Important

  Only column definitions in CREATE FOREIGN TABLE statements can specify structure data for the element data type.

  maximum-number-of-elements:

  Specifies the maximum number of array elements.

  Specify an unsigned integer literal in the range from 2 to 30,000 as the maximum number of elements.

  Example:

  • If the element data type is CHAR(5) and the maximum number of elements is 20

    CHAR(5) ARRAY[20]

  • If the element data type is INTEGER and the maximum number of elements is 5

    INTEGER ARRAY[5]

• Each element of array data is called an array element. Each array element is assigned an element number. An unsigned integer literal element number is assigned to each array element, with the element number of the first array element being 1, followed by 2, 3, ... in that order.

• An array data with zero array elements is called empty array data.

• For considerations when defining array-type columns, see Defining an array-type column [column store table] in the HADB Setup and Operation Guide.

• When structure data is specified as nested in an element data type, structure data and array data can be repeated up to eight times.

  Example: When structure data is specified eight times nested in the element data type

  STRUCT 
    {"A1" INT, 
     "A2" STRUCT 
          {"B1" INT,
           "B2" STRUCT 
                {"C1" INT,
                 "C2" STRUCT 
                      {"D1" INT,
                       "D2" INT ARRAY[2]
                      } ARRAY[3]
                } ARRAY[4]
          } ARRAY[5]
    } ARRAY[6]

To Page Top

(8) Structure data

■ STRUCT

• Structure data is a data type that handles data comprised of multiple data types.

  Important

  STRUCT type columns can only be defined in foreign tables. You cannot define a STRUCT type column in the base table.

• The following shows the format to use when specifying this data type:

  STRUCT {field-name field-data-type [,field-name field-data-type]...}

  field-name

  Specifies the name of a field in the structure data. For rules on specifying a field name, see (2) Rules for characters that can be used in names in 6.1.4 Specifying names.

  field-data-type

  Specifies the data type of the field in the structure data. Numeric data, character string data, datetime data, binary data, logical data, UUID data, array data, or structure data can be specified for the field data type.

  Note

  The combination of a field name and field data type is called a field.

  <Specification example>

  • STRUCT type comprised of the following field names and field data types

    • A1: INTEGER

    • A2: CHAR(5)

    • A3: DATE

    STRUCT {"A1" INTEGER,"A2" CHAR(5),"A3" DATE}

  • STRUCT type comprised of the following field names and field data types

    • B1: INTEGER

    • B2: CHAR(5)

    • B3: STRUCT

      A1: INTEGER

      A2: CHAR(5)

      A3: DATE

    STRUCT {"B1" INTEGER,
            "B2" CHAR(5),
            "B3" STRUCT {"A1" INTEGER,"A2" CHAR(5),"A3" DATE}}

• The maximum number of fields is 1,000. When structure data is defined in structure data, the maximum total number of fields for all structure data is 1,000.

• Field names must be unique. However, if structure data is defined within structure data, the same field name can be specified for both the structure data and its nested structure data.

• When structure data or array data is specified as nested in a field data type, structure data and array data can be repeated up to eight times. The following is an example of specifying eight times nested structure data or array data for a field data type.

  Example 1:

  STRUCT {"A1" INT, "B1" 
    STRUCT {"A2" INT, "B2"
      STRUCT {"A3" INT, "B3"
        STRUCT {"A4" INT, "B4"
          STRUCT {"A5" INT, "B5"
            STRUCT {"A6" INT, "B6"
              STRUCT {"A7" INT, "B7"
                STRUCT {"A8" INT, "B8"
                  STRUCT {"A9" INT, "B9" INT}}}}}}}}}

  Example 2:

  STRUCT {"A1" INT, "B1" 
    STRUCT {"A2" INT, "B2"
      STRUCT {"A3" INT, "B3"
        STRUCT {"A4" INT, "B4"
          STRUCT {"A5" INT, "B5"
            STRUCT {"A6" INT, "B6"
              STRUCT {"A7" INT, "B7"
                STRUCT {"A8" INT, "B8" INT} ARRAY[9]}}}}}}}

To Page Top