All Products
Search
Document Center

Hologres:Data types

Last Updated:Jul 12, 2024

The data types of Hologres are compatible with those of PostgreSQL. This topic describes the data types and array types that are supported by Hologres.

Data types

The data types of Hologres are a subset of the data types of PostgreSQL. The following table describes the data types that are supported by Hologres.

Data type

Supported version

Length

Description

Value range

Example

INTEGER (INT or INT4)

All Hologres versions

4 bytes

Common integers.

-2147483648 to 2147483647.

2147483647

BIGINT (INT8)

All Hologres versions

8 bytes

Integers in a large range.

-9223372036854775808 to 9223372036854775807.

9223372036854775807

BOOLEAN (BOOL)

All Hologres versions

1 byte

The Boolean data type.

  • True.

  • False.

True

REAL (FLOAT4)

All Hologres versions

4 bytes

The data has variable precision. The result is imprecise.

Note

In PostgreSQL, if you do not specify a precision for FLOAT, the DOUBLE PRECISION (FLOAT8) data type is used by default.

A number that has up to six decimal digits of precision.

123.123

DOUBLE PRECISION (FLOAT8)

All Hologres versions

8 bytes

The data has variable precision. The result is imprecise.

A number that has up to 15 decimal digits of precision.

123.123456789123

TEXT

All Hologres versions

Variable length

A character string with a variable length. Compared with the VARCHAR(n) and CHAR(n) data types, the TEXT data type is more flexible. We recommend that you use the TEXT data type instead of the VARCHAR(n) and CHAR(n) data types.

None.

abcdefg

TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)

All Hologres versions

8 bytes

A timestamp with a time zone. The value is accurate to the millisecond.

Note

Standard PostgreSQL uses a plus sign (+) or a minus sign (-) and a time zone offset in a value of the TIMESTAMPTZ data type to identify the time zone. If no time zone offset is specified, the system automatically adds a default time zone to the data.

4713 BC to 294276 AD.

2004-10-19 10:23:54+02

DECIMAL (NUMERIC)

All Hologres versions

Variable length

The precision and the scale must be specified.

  • Precision: the number of digits in a number. Valid values: 0 to 38.

  • Scale: the number of digits to the right of the decimal point in a number. Valid values: 0 to the value of precision.

Maximum precision: 38. Maximum scale: 38.

DECIMAL(38, 10)

DATE

Hologres V0.8 and later

4 bytes

The value is accurate to the day.

4713 BC to 5874897 AD.

2004-10-19

TIMESTAMP

Hologres V0.8 and later

8 bytes

A timestamp without a time zone. The value is accurate to the microsecond.

4713 BC to 5874897 AD.

2020-01-01 01:01:01.123456

CHAR(n)

Hologres V0.8 and later

A character string with a fixed length of n characters.

The data size must be less than or equal to 1 GB.

A character string with a fixed length.

  • abcd

  • efgh

VARCHAR(n)

Hologres V0.8 and later

A character string with a variable length that cannot exceed n characters.

The data size must be less than or equal to 1 GB.

A character string with a variable length of limited characters.

abcdefg

SERIAL (auto-increment serial)

Hologres V0.8 and later

For more information, see PostgreSQL SERIAL.

None.

None.

None

SMALLINT

Hologres V0.9 and later

2 bytes

Integers in a small range.

-32768 to 32767.

32767

JSON and JSONB

Hologres V0.9 and later

For more information, see JSON data types.

None.

None.

None

BYTEA

Hologres V0.9 and later

Variable length. For more information, see Binary Data Types.

A binary string with a variable length.

The data size must be less than or equal to 1 GB.

None

Roaring bitmap

Hologres V0.10 and later

Variable length. For more information, see Roaring bitmap functions.

An efficient array of the INT data type that supports bitmap computing of constant arrays.

None.

None

BIT(n)

Hologres V0.9 and later

A binary string with a length of n bits.

A binary string with a fixed length.

The data size must be less than or equal to 1 GB.

None

VARBIT(n)

Hologres V0.9 and later

A binary string with a variable length that cannot exceed n bits.

A binary string with a length of limited bits.

The data size must be less than or equal to 1 GB.

None

INTERVAL

All Hologres versions

16 bytes

None.

-178000000 years to 178000000 years.

interval '1 year'

TIMETZ

Hologres V0.9 and later

12 bytes

A time value with a time zone. The value is accurate to the microsecond.

00:00:00 to 24:00:00.

12:00:00+08

TIME

Hologres V0.9 and later

8 bytes

A time value without a time zone. The value is accurate to the microsecond.

00:00:00 to 24:00:00.

12:00:00

INET

Hologres V0.9 and later

For more information, see Network address types.

This data type allows you to save an IPv4 or IPv6 host address in a data domain.

None.

192.168.100.128/25

MONEY

Hologres V0.9 and later

8 bytes. For more information, see Monetary types.

This data type allows you to store a currency amount with a fixed fractional precision.

-92233720368547758.08 to +92233720368547758.07.

$12.34

OID

Hologres V0.9 and later

4 bytes

An object identifier in the numeric form.

None.

1024

UUID

Hologres V0.9 and later

16 bytes

A universally unique identifier with a fixed length of 128 bits.

Note

Algorithms provided by the uuid-ossp module are not supported. For more information, see UUID Type.

00000000-0000-0000-0000-000000000000 to ffffffff-ffff-ffff-ffff-ffffffffffff.

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

The following sample SQL statements provide examples of the TIMESTAMP WITH TIME ZONE, DATE, and DECIMAL data types:

CREATE TABLE test_data_type (
  tswtz_column TIMESTAMP WITH TIME ZONE,
  date_column date,
  decimal_column decimal(38, 10),
  char_column char(20),
  varchar_volumn varchar(225)
);

INSERT INTO test_data_type
VALUES ('2004-10-19 08:08:08', '2004-10-19', 123.456, 'abcd', 'a');

SELECT * FROM test_data_type;
      tswtz_column      | date_column | decimal_column |     char_column      | varchar_volumn 
------------------------+-------------+----------------+----------------------+----------------
 2004-10-19 08:08:08+08 | 2004-10-19  | 123.4560000000 | abcd                 | a
(1 row)

The following sample SQL statements provide examples of the BIT, VARBIT, and BYTEA data types:

// BIT and VARBIT data types

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');

ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;

  a  |  b
-----+-----
 101 | 00
 100 | 101

//BYTEA data type

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T
 
RESET bytea_output;  -- 'hex' by default

SELECT 'abc \153\154\155 \052\251\124'::bytea;
          bytea
--------------------------
 \x616263206b6c6d202aa954
(1 row)

Array types

Hologres supports one-dimensional arrays of the following types:

  • int4[]

  • int8[]

  • float4[]

  • float8[]

  • boolean[]

  • text[]

The following sample SQL statements provide examples on how to use the preceding array types:

  • Declare arrays.

    CREATE TABLE array_example(
    int4_array int4[],
    int8_array int8[],
    float4_array float4[],
    float8_array float8[],
    boolean_array boolean[],
    text_array text[]);
  • Insert arrays.

    • Use the ARRAY keyword.

      INSERT INTO array_example(
      int4_array,
      int8_array,
      float4_array,
      float8_array,
      boolean_array,
      text_array)
      VALUES (ARRAY[1, 2, 3, 4],
      ARRAY[1, 2, 3, 4],
      ARRAY[1.0, 2.0],
      ARRAY[1.0, 2.0, 3.0],
      ARRAY[true, true, false],
      ARRAY['foo1', 'foo2', 'foo3']);
    • Use the {} expression.

      INSERT INTO array_example(
      int4_array,
      int8_array,
      float4_array,
      float8_array,
      boolean_array,
      text_array)
      VALUES ('{1, 2, 3, 4}',
      '{1, 2, 3, 4}',
      '{1.0, 2.0}',
      '{1.0, 2.0, 3.0}',
      '{true, true, false}',
      '{"foo1", "foo2", "foo3"}');
  • Query data from an array.

    • Query an element in an array.

      SELECT int4_array[3] FROM array_example;
    • Query multiple elements in an array.

      SELECT int4_array[1:2] FROM array_example;

Data type mappings between MaxCompute and Hologres

The following table describes the data type mappings between MaxCompute and Hologres when you create a foreign table that is sourced from a MaxCompute table.

Data type supported by MaxCompute

Data type supported by Hologres

Supported version

Description

  • STRING

  • VARCHAR

TEXT

All Hologres versions.

None.

BIGINT

INT8

All Hologres versions.

None.

INT

  • INT4

  • INT

All Hologres versions.

None.

FLOAT

  • FLOAT4

  • REAL

All Hologres versions.

None.

DOUBLE

  • FLOAT

  • FLOAT8

All Hologres versions.

None.

BOOLEAN

BOOL

All Hologres versions.

None.

DATETIME

TIMESTAMP WITH TIME ZONE

All Hologres versions.

The DATETIME data type of MaxCompute specifies a date time in UTC+8. The time ranges from 0000-01-01 to 9999-12-31, and is accurate to the millisecond.

DECIMAL

NUMERIC

All Hologres versions.

If no precision or scale is specified for the DECIMAL data type in MaxCompute, DECIMAL (38,18) is used by default. The precision and the scale are automatically converted when you create a foreign table by executing the IMPORT FOREIGN SCHEMA statement. For more information about the statement, see IMPORT FOREIGN SCHEMA.

TIMESTAMP

TIMESTAMP WITH TIME ZONE

Hologres V0.8 and later.

  • Valid values of the TIMESTAMP data type of MaxCompute are 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. The timestamp is accurate to the nanosecond.

  • Data of the TIMESTAMPTZ data type of Hologres is accurate to the millisecond.

    Hologres automatically converts the precision to the millisecond when Hologres reads data.

CHAR(n)

By default, CHAR(n) is used.

Hologres also allows you to map CHAR(n) in MaxCompute to TEXT. To map to TEXT, you must add the set hg_enable_convert_type_for_foreign_table = true configuration, and set the data type to TEXT when you create a foreign table.

Hologres V0.8 and later.

Entries of the CHAR(n) data type in MaxCompute are character strings with a fixed length of n characters. The maximum value of n is 255. If you insert a character string that is shorter than the required length, Hologres adds spaces to the character string to increase the length.

VARCHAR(n)

By default, VARCHAR(n) is used.

Hologres also allows you to map VARCHAR(n) in MaxCompute to TEXT. To map to TEXT, you must add the set hg_enable_convert_type_for_foreign_table = true configuration, and set the data type to TEXT when you create a foreign table.

Hologres V0.8 and later.

Entries of the VARCHAR(n) data type in MaxCompute are character strings with a variable length of n characters. Valid values of n: 1 to 65535.

DATE

DATE

Hologres V0.8 and later.

None.

SMALLINT

By default, INT2 is used.

Hologres also allows you to map SMALLINT in MaxCompute to INT8. To map to INT8, you must add the set hg_enable_convert_type_for_foreign_table = true configuration, and set the data type to INT8 when you create a foreign table.

All Hologres versions, in which the data type for Hologres V0.8 is INT4 and the data type for Hologres V0.9 is INT2.

None.

TINYINT

By default, INT2 is used.

Hologres also allows you to map TINYINT in MaxCompute to INT8. To map to INT8, you must add the set hg_enable_convert_type_for_foreign_table = true configuration, and set the data type to INT8 when you create a foreign table.

All Hologres versions, in which the data type for Hologres V0.8 is INT4 and the data type for Hologres V0.9 is INT2.

None.

CHAR

Not supported.

Not supported.

None.

ARRAY<INT>

INT4[]

Hologres V0.8 and later.

None.

ARRAY<BIGINT>

INT8[]

Hologres V0.8 and later.

None.

ARRAY<FLOAT>

FLOAT4[]

Hologres V0.8 and later.

None.

ARRAY<DOUBLE>

FLOAT8[]

Hologres V0.8 and later.

None.

ARRAY<BOOLEAN>

BOOLEAN[]

Hologres V0.8 and later.

None.

ARRAY<STRING>

TEXT[]

Hologres V0.8 and later.

None.

BINARY

BYTEA

Hologres V0.9 and later.

None.

ARRAY<TINYINT>

Not supported.

Not supported.

None.

ARRAY<SMALLINT>

Not supported.

Not supported.

None.

Note

For a MaxCompute table that contains a field whose data type is not supported by Hologres, if you do not specify this field in a query, data of other fields can be queried.

Data type mappings between Realtime Compute for Apache Flink or Blink and Hologres

The following table describes the data type mappings between Realtime Compute for Apache Flink or Blink and Hologres.

Note

Realtime Compute for Apache Flink or Blink supports only some data types of Hologres binary log source tables. For more information, see Use Realtime Compute for Apache Flink or Blink to consume Hologres binary log data in real time.

Data type supported by Realtime Compute for Apache Flink or Blink

Data type supported by Hologres

Supported Hologres version

Supported Realtime Compute for Apache Flink or Blink version

INT

  • INT4

  • INT

All Hologres versions.

All versions.

BIGINT

INT8

All Hologres versions.

All versions.

VARCHAR

TEXT

All Hologres versions.

All versions.

DOUBLE

  • FLOAT

  • FLOAT8

  • DOUBLE PRECISION

All Hologres versions.

All versions.

BOOLEAN

BOOL

All Hologres versions.

All versions.

DECIMAL

NUMERIC

All Hologres versions.

All versions.

DATE

DATE

Hologres V0.8 and later.

All versions.

TIMESTAMP

TIMESTAMP WITH TIME ZONE

All Hologres versions.

All versions.

FLOAT

  • FLOAT4

  • REAL

All Hologres versions.

All versions.

TIME

TIME

All Hologres versions.

  • Result table: Realtime Compute for Apache Flink VVR-4.0.13-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

    Note

    We recommend that you do not use the TIME type because the fixed plan feature does not support the TIME data type. For more information, see Accelerate the execution of SQL statements by using fixed plans.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: Realtime Compute for Apache Flink VVR-4.0.13-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

VARCHAR

JSONB

Hologres V0.10 and later.

  • Result table: Realtime Compute for Apache Flink VVR-4.0.12-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: Realtime Compute for Apache Flink VVR-4.0.12-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

VARCHAR

JSON

Hologres V0.9 and later.

  • Result table: Realtime Compute for Apache Flink VVR-4.0.12-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: Realtime Compute for Apache Flink VVR-4.0.12-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

BYTES

Roaring bitmap

Hologres V0.10 and later.

  • Result table: Realtime Compute for Apache Flink VVR-4.0.12-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: Realtime Compute for Apache Flink VVR-4.0.12-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

TINYINT

SMALLINT

All Hologres versions.

  • Result table: Realtime Compute for Apache Flink VVR-4.0.13-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: Realtime Compute for Apache Flink VVR-4.0.13-Flink-1.13 and later. Data that is written in RPC mode does not support this data type.

SMALLINT

SMALLINT

All Hologres versions.

  • Result table: all versions.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions.

ARRAY<INT>

int4[]

Hologres V0.8 and later.

  • Result table: all versions.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions.

ARRAY<BIGINT>

int8[]

Hologres V0.8 and later.

  • Result table: all versions.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions.

ARRAY<FLOAT>

float4[]

Hologres V0.8 and later.

  • Result table: all versions.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions.

ARRAY<DOUBLE>

float8[]

Hologres V0.8 and later.

  • Result table: all versions.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions.

ARRAY<BOOLEAN>

boolean[]

Hologres V0.8 and later.

  • Result table: all versions.

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions.

ARRAY<VARCHAR>

TEXT[]

Hologres V0.8 and later.

  • Result table: all versions

  • Source table: Realtime Compute for Apache Flink VVR-6.0.3-Flink-1.15 and later. Data that is written in RPC mode does not support this data type.

  • Dimension table: all versions

CHAR

Not supported

Not supported.

Not supported.

BINARY

Not supported

Not supported.

Not supported.

Data type mappings between MySQL and Hologres

The following table describes the data type mappings between MySQL and Hologres. For more information about how to migrate data from MySQL to Hologres, see Migrate data from MySQL to Hologres.

Data type supported by MySQL

Data type supported by Hologres

BIGINT

BIGINT

BINARY(n)

BYTEA

BIT

BOOLEAN

  • CHAR(n)

  • CHARACTER(n)

  • CHAR(n)

  • CHARACTER(n)

DATE

DATE

DATETIME

TIMESTAMP [WITHOUT TIME ZONE]

  • DECIMAL(p,s)

  • DEC(p,s)

  • DECIMAL(p,s)

  • DEC(p,s)

DOUBLE

DOUBLE PRECISION

FLOAT

REAL

  • INT

  • INTEGER

  • INT

  • INTEGER

MEDIUMINT

INTEGER

NUMERIC(p,s)

NUMERIC(p,s)

SMALLINT

SMALLINT

  • TINYBLOB

  • BLOB

  • MEDIUMBLOB

  • LONGBLOB

BYTEA

TINYINT

SMALLINT

  • TINYTEXT

  • TEXT

  • MEDIUMTEXT

  • LONGTEXT

TEXT

TIME

TIME [WITHOUT TIME ZONE]

TIMESTAMP

TIMESTAMP [WITH TIME ZONE]

  • VARBINARY(n)

  • VARBINARY(max)

BYTEA

VARCHAR(n)

VARCHAR(n)

VARCHAR(max)

TEXT

Data type mappings between Data Lake Formation (DLF) and Hologres

Data type supported by DLF

Data type supported by Hologres

TINYINT

SMALLINT

SMALLINT

SMALLINT

INT

INT

BIGINT

BIGINT

BOOLEAN

BOOLEAN

FLOAT

REAL

DOUBLE

DOUBLE PRECISION

DATE

DATE

TIMESTAMP

TIMESTAMP WITHOUT TIME ZONE

STRING

TEXT

BINARY

BYTEA

DECIMAL(m,n)

NUMERIC(m,n)

VARCHAR(n)

CHARACTER VARYING(n)

CHAR(n)

CHARACTOR(n)

ARRAY<type>

ARRAY<hologres_data_type>

The following data types are supported:

  • INT

  • BIGINT

  • FLOAT

  • BOOLEAN

  • DOUBLE

  • STRING

Data type mappings between Hive and Hologres

Data type supported by Hive

Data type supported by Hologres

TINYINT

SMALLINT

SMALLINT

SMALLINT

INT

INT

BIGINT

BIGINT

FLOAT

REAL

DOUBLE

DOUBLE PRECISION

DECIMAL

NUMERIC

NUMERIC

NUMERIC

DATE

DATE

TIMESTAMP

TIMESTAMP WITHOUT TIME ZONE

STRING

TEXT

VARCHAR

VARCHAR

CHAR

CHAR

BINARY

BYTEA

BOOL

BOOLEAN

ARRAY<type>

ARRAY<hologres_data_type>

The following data types are supported:

  • INT

  • BIGINT

  • FLOAT

  • BOOLEAN

  • DOUBLE PRECISION

  • STRING

Data type mappings between Apache Hudi and Hologres

The following table describes the data type mappings between Apache Hudi and Hologres V1.3 and later.

Data type supported by Apache Hudi

Data type supported by Hologres

IntegerType

INT

LongType

BIGINT

FloatType

REAL

DoubleType

DOUBLE PRECISION

DecimalType

NUMERIC

TimestampType

TIMESTAMP WITHOUT TIME ZONE

DateType

DATE

YearMonthIntervalType

Not supported

DayTimeIntervalType

Not supported

StringType

TEXT

VarcharType

Not supported

CharType

Not supported

BooleanType

BOOL

BinaryType

BYTEA

ByteType

Not supported

ShortType

Not supported

ArrayType(elementType, containsNull)

ARRAY<hologres_data_type>

The following data types are supported:

  • INT

  • BIGINT

  • FLOAT

  • BOOLEAN

  • DOUBLE PRECISION

  • STRING

Data type mappings between Delta Lake and Hologres

The following table describes the data type mappings between Delta Lake and Hologres V1.3 and later.

Data type supported by Delta Lake

Data type supported by Hologres

TINYINT

SMALLINT

SMALLINT

SMALLINT

INT

INT

BIGINT

BIGINT

FLOAT

REAL

DOUBLE

DOUBLE PRECISION

DECIMAL(p,s)

NUMERIC

TIMESTAMP

TIMESTAMP WITHOUT TIME ZONE

DATE

DATE

INTERVAL intervalQualifier

Not supported

STRING

TEXT

BOOLEAN

BOOLEAN

BINARY

BYTEA

ARRAY<elementType>

ARRAY<hologres_data_type>

The following data types are supported:

  • INT

  • BIGINT

  • FLOAT

  • BOOLEAN

  • DOUBLE PRECISION

  • STRING

Data type mappings between Paimon and Hologres

Data type supported by Paimon

Data type supported by Hologres

TINYINT

SMALLINT

SMALLINT

SMALLINT

INT

INT

BIGINT

BIGINT

FLOAT

REAL

DOUBLE

DOUBLE PRECISION

DECIMAL(p,s)

DECIMAL

TIMESTAMP

TIMESTAMP WITHOUT TIME ZONE

DATE

DATE

CHAR

CHAR

VARCHAR

VARCHAR

BINARY

BYTEA

ARRAY

ARRAY<hologres_data_type>

The following data types are supported:

  • INT

  • BIGINT

  • FLOAT

  • BOOLEAN

  • DOUBLE PRECISION

  • STRING