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 of Hologres

Hologres supports some of the data types of PostgreSQL. The following table describes the data types that are supported by Hologres.
Data type Supported version Length Description Valid value Example
INTEGER (alias: INT or INT4) All Hologres versions 4 bytes Common integers. -2147483648 to +2147483647 2147483647
BIGINT (alias: INT8) All Hologres versions 8 bytes Integers in a large range. -9223372036854775808 to +9223372036854775807 9223372036854775807
BOOLEAN (alias: BOOL) All Hologres versions 1 byte The Boolean data type.
  • True
  • False
True
REAL (alias: FLOAT4) All Hologres versions 4 bytes The data has variable precision. If the precision is low, the result is imprecise.
Note In PostgreSQL, the REAL data type without setting the precision is equivalent to the data type of DOUBLE PRECISION (FLOAT8).
A number that has up to six decimal digits of precision. 123.123
DOUBLE PRECISION (alias: FLOAT8) All Hologres versions 8 bytes The data has variable precision. If the precision is low, the result is imprecise. A number that has up to 15 decimal digits of precision. 123.123456789123
TEXT (alias: VARCHAR) All Hologres versions Variable length. A character string with a variable length. None abcdefg
TIMESTAMP WITH TIME ZONE (alias: TIMESTAMPTZ) All Hologres versions 8 bytes A timestamp with a time zone. Unit: milliseconds.
Note PostgreSQL identifies the time zone in UTC by using a plus sign (+) or a minus sign (-) and a time zone offset after the time in a value of the TIMESTAMPTZ data type. If no time zone is specified in the input string, the system automatically adds a default time zone to the data.
4713 BC to 294276 AD 2004-10-19 10:23:54+02
DECIMAL (alias: 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 and scale: 38. DECIMAL(38, 10)
DATE Hologres V0.8 4 bytes Unit: days. 4713 BC ~ 5874897 AD 2004-10-19
TIMESTAMP Hologres V0.8 8 bytes A timestamp without a time zone. Unit: microseconds. 4713 BC ~ 5874897 AD 2020-01-01 01:01:01.123456
CHAR(n) Hologres V0.8 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 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 column) Hologres V0.8 For more information, see PostgreSQL SERIAL. None None None
SMALLINT Hologres V0.9 2 bytes Integers in a small range. -32768~+32767 32767
JSON and JSONB Hologres V0.9 For more information, see JSON data types. None None None
BYTEA Hologres V0.9 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
RoaringBitmap Hologres V0.10 Variable length. For more information, see Roaring bitmap functions. An efficient array of the INT data type that supports bitmap calculation of constant arrays. None None
BIT(n) Hologres V0.9 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 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~178000000 years interval '1 year'
TIMETZ Hologres V0.9 12 bytes A timestamp with a time zone. Unit: microseconds. 00:00:00~24:00:00 12:00:00+08
TIME Hologres V0.9 8 bytes A timestamp without a time zone. Unit: microseconds. 00:00:00~24:00:00 12:00:00
INET Hologres V0.9 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 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~+92233720368547758.07 $12.34
OID Hologres V0.9 4 bytes An object identifier in the numeric form. None 1024
UUID Hologres V0.9 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~ffffffff-ffff-ffff-ffff-ffffffffffff a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
The following sample SQL statements provide an example 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 an example of the BIT, VARBIT, and BYTEA data types:
// The 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

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 an example on how to use the preceding array types:
  • Declare an array.
    CREATE TABLE array_example(
    int4_array int4[],
    int8_array int8[],
    float4_array float4[],
    float8_array float8[],
    boolean_array boolean[],
    text_array text[]);
  • Add an element to an array.
    • 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 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 when you create a foreign table

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.
MaxCompute data type Hologres data type 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 milliseconds.
DECIMAL NUMERIC All Hologres versions If no precision or scale is specified for the DECIMAL data type in MaxCompute, the default value is DECIMAL (38,18). 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
  • 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 nanoseconds.
  • The timestamp of the TIMESTAMPTZ data type of Hologres is accurate to milliseconds.
The precision and the scale are automatically converted.
CHAR(n) By default, CHAR(n) is used.

Hologres allows you to map CHAR(n) in MaxCompute to TEXT. You must set the hg_enable_convert_type_for_foreign_table parameter to true, and set the data type to TEXT when you create a foreign table.

Hologres V0.8 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 increases the length by using spaces.
VARCHAR(n) By default, VARCHAR(n) is used.

Hologres allows you to map VARCHAR(n) in MaxCompute to TEXT. You must set the hg_enable_convert_type_for_foreign_table parameter to true, and set the data type to TEXT when you create a foreign table.

Hologres V0.8 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 None
SMALLINT By default, INT2 is used.

Hologres allows you to map SMALLINT in MaxCompute to INT8. You must set the hg_enable_convert_type_for_foreign_table parameter to true, 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 allows you to map TINYINT in MaxCompute to INT8. You must set the hg_enable_convert_type_for_foreign_table parameter to true, 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 None
ARRAY<BIGINT> INT8[] Hologres V0.8 None
ARRAY<FLOAT> FLOAT4[] Hologres V0.8 None
ARRAY<DOUBLE> FLOAT8[] Hologres V0.8 None
ARRAY<BOOLEAN> BOOLEAN[] Hologres V0.8 None
ARRAY<STRING> TEXT[] Hologres V0.8 None
BINARY BYTEA Hologres V0.9 None
ARRAY<TINYINT> Not supported Not supported None
ARRAY<SMALLINT> Not supported Not supported None
Note If the MaxCompute table contains fields whose data types are not supported by Hologres, queries of other fields are not affected.

Data type mappings between Realtime Compute for Apache Flink and Hologres

The following table describes the data type mappings between Realtime Compute for Apache Flink and Hologres.
Note Only some data types are supported in Hologres binary source tables. For more information, see Use Realtime Compute for Apache Flink or Blink-based Realtime Compute to consume Hologres binary logs in real time.
Realtime Compute data type Hologres data type Supported version
VARCHAR TEXT All Hologres versions
BIGINT INT8 All Hologres versions
INT
  • INT4
  • INT
All Hologres versions
SMALLINT SMALLINT All Hologres versions
TINYINT SMALLINT All Hologres versions
FLOAT
  • FLOAT4
  • REAL
All Hologres versions
DOUBLE
  • FLOAT
  • FLOAT8
  • DOUBLE PRECISION
All Hologres versions
BOOLEAN BOOL All Hologres versions
TIMESTAMP TIMESTAMP WITH TIME ZONE All Hologres versions
DATE DATE Hologres V0.8
DECIMAL NUMERIC All Hologres versions
TIME TIME Hologres versions using Flink engine of vvr-4.0.13-flink-1.13 or later
CHAR Not supported Not supported
BINARY Not supported Not supported
VARCHAR JSON Hologres V0.9
VARCHAR JSONB Hologres V0.10
BYTES RoaringBitmap Hologres V0.10 using Flink engine of vvr-4.0.12-flink-1.13 or later
ARRAY<INT> INT[] Hologres V0.8
ARRAY<BIGINT> BIGINT[] Hologres V0.8
ARRAY<FLOAT> REAL[] Hologres V0.8
ARRAY<DOUBLE> DOUBLE PRECISION[] Hologres V0.8
ARRAY<BOOLEAN> BOOLEAN[] Hologres V0.8
ARRAY<VARCHAR> TEXT[] Hologres V0.8

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.

MySQL data type Hologres data type
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 and Hologres

Data Lake Formation data type Hologres data type
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

Hive data type Hologres data type
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 Hudi and Hologres

Hologres V1.3 and later support the Hudi data types in the following table.

Hudi data type Hologres data type
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 and Hologres

Hologres V1.3 and later support the Delta data types in the following table.

Delta Lake data type Hologres data type
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