Data types

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

Hologres data types are a subset of PostgreSQL data types. The following table lists the supported data types.

Data type	Supported version	Length	Description	Value range	Example
INTEGER (INT or INT4)	All Hologres versions	4 bytes	A standard integer.	-2147483648 to +2147483647	2147483647
BIGINT (INT8)	All Hologres versions	8 bytes	A large-range integer.	-9223372036854775808 to +9223372036854775807	9223372036854775807
BOOLEAN (BOOL)	All Hologres versions	1 byte	A Boolean type.	true false	true
REAL (FLOAT4)	All Hologres versions	4 bytes	A variable-precision, inexact number. Note In the PostgreSQL ecosystem, if you do not specify the precision for the `FLOAT` type, it defaults to `DOUBLE PRECISION` (`FLOAT8`).	6-digit decimal precision	123.123
DOUBLE PRECISION (FLOAT8)	All Hologres versions	8 bytes	A variable-precision, inexact number.	15-digit decimal precision	123.123456789123
TEXT	All Hologres versions	Variable length	A variable-length string. Use the `TEXT` type instead of `VARCHAR(n)` or `CHAR(n)` because it is more flexible.	None	abcdefg
TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)	All Hologres versions	8 bytes	A timestamp with a time zone. The resolution is in milliseconds. Note In standard PostgreSQL, a `TIMESTAMP WITH TIME ZONE` value uses a `+` or `-` symbol for the time zone offset. If you do not specify an offset, the system applies its default time zone.	4713 BC to 294276 AD	2004-10-19 10:23:54+02
DECIMAL (NUMERIC)	All Hologres versions	Variable	You must specify the `PRECISION` and `SCALE` `PRECISION`: The total number of digits. The value must be in the range of 1 to 38. `SCALE`: The number of digits in the fractional part. The value must be in the range of 0 to `PRECISION`.	Up to 38 digits in total, including digits before and after the decimal point.	DECIMAL(38, 10)
DATE	Hologres V0.8 and later	4 bytes	A date.	4713 BC to 5874897 AD	2004-10-19
TIMESTAMP	Hologres V0.8 and later	8 bytes	A timestamp without a time zone. The resolution is in microseconds.	4713 BC to 5874897 AD	2020-01-01 01:01:01.123456
CHAR(n)	Hologres V0.8 and later	Fixed-length, n characters.	A fixed-length string. If a string is shorter than `n`, it is padded with spaces.	The storage size cannot exceed 1 GB.	abcd efgh
VARCHAR(n)	Hologres V0.8 and later	Variable-length, up to n characters.	A variable-length string with a length limit.	The storage size cannot exceed 1 GB.	abcdefg
SERIAL	Hologres V0.8 and later	For more information, see PostgreSQL SERIAL.	An auto-incrementing integer sequence.	None	None
SMALLINT	Hologres V0.9 and later	2 bytes	A small-range integer.	-32768 to +32767	32767
JSON and JSONB	Hologres V0.9 and later	See JSON and JSONB data types.	A data type for storing JSON data.	None	None
BYTEA	Hologres V0.9 and later	Variable. See Binary Data Types.	A variable-length binary string.	The storage size cannot exceed 1 GB.	None
RoaringBitmap	Hologres V0.10 and later	Variable. See Roaring bitmap functions.	An efficient data structure for storing a compressed bitmap of `INT` values. Supports constant-time bitmap operations.	None	None
RoaringBitmap64	Hologres V3.1 and later	Variable. See Roaring bitmap functions.	An efficient data structure for storing a compressed bitmap of `BIGINT` values. Supports constant-time bitmap operations.	None	None
BIT(n)	Hologres V0.9 and later	`n` bits.	A fixed-length bit string.	The storage size cannot exceed 1 GB.	None
VARBIT(n)	Hologres V0.9 and later	Variable-length, up to `n` bits.	A variable-length bit string with a length limit.	The storage size cannot exceed 1 GB.	None
INTERVAL	All Hologres versions	16 bytes	A time span.	-178000000 years to 178000000 years	interval '1 year'
TIMETZ	Hologres V0.9 and later	12 bytes	The time of day with a time zone. The resolution is in microseconds.	00:00:00 to 24:00:00	12:00:00+08
TIME	Hologres V0.9 and later	8 bytes	The time of day without a time zone. The resolution is in microseconds.	00:00:00 to 24:00:00	12:00:00
INET	Hologres V0.9 and later	For more information, see Network Address Types.	An IPv4 or IPv6 host address.	None	192.168.100.128/25
MONEY	Hologres V0.9 and later	8 bytes. See Monetary types.	A currency amount with a fixed fractional precision.	-92233720368547758.08 to +92233720368547758.07	$12.34
OID	Hologres V0.9 and later	4 bytes	A numeric object identifier.	None	1024
UUID	Hologres V0.9 and later	16 bytes	A universally unique identifier with a fixed length of 128 bits. Note The algorithms implemented in `uuid-ossp` are not currently supported. See UUID Type.	00000000-0000-0000-0000-000000000000 to ffffffff-ffff-ffff-ffff-ffffffffffff	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

The following SQL statements show examples of TIMESTAMP WITH TIME ZONE, DATE, and DECIMAL.

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 SQL statements show examples of BIT, VARBIT, and BYTEA.

// 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 currently supports only the following one-dimensional arrays:

int4[]
int8[]
float4[]
float8[]
boolean[]
text[]

Examples:

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[]);

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 an array.
- Query a single 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 to access MaxCompute data, the following table shows the data type mappings between MaxCompute and Hologres.

MaxCompute data type	Hologres data type	Supported Hologres version	Description
STRING VARCHAR	TEXT	All versions.
BIGINT	INT8	All versions.
INT	INT4 INT	All versions.
FLOAT	FLOAT4 REAL	All versions.
DOUBLE	FLOAT FLOAT8	All versions.
BOOLEAN	BOOL	All versions.
DATETIME	TIMESTAMP WITH TIME ZONE	All versions.	The `DATETIME` type in MaxCompute uses the UTC+8 time zone. The value range is from 0000-01-01 to 9999-12-31, with millisecond precision.
DECIMAL	NUMERIC	All versions.	If you do not specify the precision for the `DECIMAL` type in MaxCompute, it defaults to `(38, 18)`. When you create a table using IMPORT FOREIGN SCHEMA, the system automatically converts the precision.
TIMESTAMP	TIMESTAMP WITH TIME ZONE	Hologres V0.8 and later.	The value range of the `TIMESTAMP` type in MaxCompute is `0000-01-01 00:00:00.000000000` to `9999-12-31 23:59:59.999999999`, with nanosecond precision. Hologres internally handles this precision conversion, automatically truncating values to millisecond precision during reads.
CHAR(n)	Defaults to `CHAR(n)`. You can also map it to `TEXT`. To do this, execute `set hg_enable_convert_type_for_foreign_table = true;` and specify `TEXT` as the field type when creating the table.	Hologres V0.8 and later.	The `CHAR(n)` type in MaxCompute is a fixed-length character type, where `n` is the length with a maximum value of 255. If the stored string is shorter than `n`, it is padded with spaces.
VARCHAR(n)	Defaults to `VARCHAR(n)`. You can also map it to `TEXT`. To do this, execute `set hg_enable_convert_type_for_foreign_table = true;` and specify `TEXT` as the field type when creating the table.	Hologres V0.8 and later.	The `VARCHAR(n)` type in MaxCompute is a variable-length character type, where `n` is the length with a value range of 1 to 65,535.
DATE	DATE	Hologres V0.8 and later.
SMALLINT	Defaults to `INT2`. You can also map it to `INT8`. To do this, execute `set hg_enable_convert_type_for_foreign_table = true;` and specify `INT8` as the field type when creating the table.	All versions. Maps to `INT4` in V0.8 and to `INT2` in V0.9 and later.
TINYINT	Defaults to `INT2`. You can also map it to `INT8`. To do this, execute `set hg_enable_convert_type_for_foreign_table = true;` and specify `INT8` as the field type when creating the table.	All versions. Maps to `INT4` in V0.8 and to `INT2` in V0.9 and later.
CHAR	Not supported.	N/A
ARRAY<INT>	INT4[]	Hologres V0.8 and later.
ARRAY<BIGINT>	INT8[]	Hologres V0.8 and later.
ARRAY<FLOAT>	FLOAT4[]	Hologres V0.8 and later.
ARRAY<DOUBLE>	FLOAT8[]	Hologres V0.8 and later.
ARRAY<BOOLEAN>	BOOLEAN[]	Hologres V0.8 and later.
ARRAY<STRING>	TEXT[]	Hologres V0.8 and later.
BINARY	BYTEA	Hologres V0.9 and later.
ARRAY<TINYINT>	Not supported.	Not supported.
ARRAY<SMALLINT>	Not supported.	Not supported.

Note

If a MaxCompute table contains fields of a data type that Hologres does not support, you can still query the table as long as your query does not access the unsupported fields.

Data type mappings between Flink/Blink and Hologres

The following table shows the data type mappings between Blink/Flink and Hologres.

Note

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

Flink/Blink data type	Hologres data type	Supported Hologres version	Supported Flink/Blink version
INT	INT4 INT	All versions.	All versions.
BIGINT	INT8	All versions.	All versions.
VARCHAR	TEXT	All versions.	All versions.
DOUBLE	FLOAT FLOAT8 DOUBLE PRECISION	All versions.	All versions.
BOOLEAN	BOOL	All versions.	All versions.
DECIMAL	NUMERIC Note When you use CTAS to synchronize data to Hologres: A primary key of the `DECIMAL` type maps to the `TEXT` type. A non-primary-key field of the `DECIMAL` type maps to the `DECIMAL` type. For more information, see FAQ about connectors	All versions.	All versions.
DATE	DATE	Hologres V0.8 and later.	All versions.
TIMESTAMP	TIMESTAMP WITH TIME ZONE	All versions.	All versions.
FLOAT	FLOAT4 REAL	All versions.	All versions.
TIME	TIME and TIMETZ	All versions. Note In Hologres V2.1.24 or later, the TIME and TIMETZ data types are supported when you use the fixed plan feature to accelerate the execution of SQL statements. For more information, see Accelerate the execution of SQL statements by using fixed plans.	Sink table: Supported by Flink engine VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported. Note Fixed plan does not support the TIME data type. For more information, see Accelerate the execution of SQL statements by using fixed plans. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: Supported by Flink engine VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported.
VARCHAR	JSONB	Hologres V0.10 and later.	Sink table: Supported by Flink engine VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: Supported by Flink engine VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported.
VARCHAR	JSON	Hologres V0.9 and later.	Sink table: Supported by Flink engine VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: Supported by Flink engine VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported.
BYTES	RoaringBitmap	Hologres V0.10 and later.	Sink table: Supported by Flink engine VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: Supported by Flink engine VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported.
VARCHAR	GEOMETRY and GEOGRAPHY	All versions. Note As of Hologres v2.1, Fixed Plan supports writing data of the `GEOMETRY` and `GEOGRAPHY` types.	Sink table: Supported by Flink engine VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported. Source table: Not supported. Dimension table: Not supported.
TINYINT	SMALLINT	All versions.	Sink table: Supported by Flink engine VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: Supported by Flink engine VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported.
SMALLINT	SMALLINT	All versions.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: All versions.
ARRAY<INT>	int4[]	Hologres V0.8 and later.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: all versions.
ARRAY<BIGINT>	int8[]	Hologres V0.8 and later.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: All versions.
ARRAY<FLOAT>	float4[]	Hologres V0.8 and later.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: All versions.
ARRAY<DOUBLE>	float8[]	Hologres V0.8 and later.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: All versions.
ARRAY<BOOLEAN>	boolean[]	Hologres V0.8 and later.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: All versions.
ARRAY<VARCHAR>	TEXT[]	Hologres V0.8 and later.	Sink table: All versions. Source table: Supported by Flink engine VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension table: All versions.
CHAR	Not supported.	N/A	Not supported.
BINARY	Not supported.	N/A	Not supported.

Data type mappings between MySQL and Hologres

The following table shows the data type mappings between MySQL and Hologres. For more information, 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 (DLF) and Hologres

DLF 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 Apache Hudi and Hologres

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 Lake and Hologres

This mapping is supported in Hologres v1.3 and later.

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

Data type mappings between Paimon and Hologres

Paimon data type	Hologres data type
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

Data type mappings between Iceberg and Hologres

Iceberg data type	Hologres data type
BOOLEAN	BOOLEAN
INT	INTEGER
LONG	BIGINT
FLOAT	REAL
DOUBLE	DOUBLE PRECISION
DECIMAL(P,S)	NUMERIC(P,S)
DATE	DATE
TIME	TEXT. The `TIME` type is not supported by Spark and is converted to a `STRING` by Flink when written to Iceberg tables.
TIMESTAMP	TIMESTAMP WITHOUT TIME ZONE
TIMESTAMPTZ	Not supported.
STRING	TEXT
UUID	Not supported. Writers like Flink and Spark cannot write this type to Iceberg.
FIXED(L)	BYTEA
BINARY	BYTEA
LIST	ARRAY<hologres_data_type> The following data types are supported: INT BIGINT FLOAT BOOLEAN DOUBLE PRECISION STRING
STRUCT	Not supported.
MAP	Not supported.