Documentation
Home Page Hologres Developer Reference Hologres SQL Data types Data types overview

Data types overview

Updated at:
Copy as MD
Product

Hologres is compatible with PostgreSQL data types and supports a subset of them. This page covers the native data types, one-dimensional array types, and type mappings from upstream systems including MaxCompute, Flink, MySQL, DLF, Hive, Hudi, Delta Lake, Paimon, and Iceberg.

Data types

The following table lists all data types supported by Hologres. All type names and aliases are case-insensitive in SQL but are shown in uppercase by convention.

Name

Aliases

Storage size

Description

Value range

Example

INTEGER

INT, INT4

4 bytes

Common integer.

-2,147,483,648 to +2,147,483,647

2147483647

BIGINT

INT8

8 bytes

Large-range integer.

-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807

9223372036854775807

SMALLINT

—

2 bytes

Small-range integer.

-32,768 to +32,767

32767

REAL

FLOAT4

4 bytes

Variable-precision floating point, inexact. In the PostgreSQL ecosystem, FLOAT without a specified precision defaults to DOUBLE PRECISION (FLOAT8).

6 decimal digits of precision

123.123

DOUBLE PRECISION

FLOAT8

8 bytes

Variable-precision floating point, inexact.

15 decimal digits of precision

123.123456789123

DECIMAL

NUMERIC

Variable

Exact numeric. Specify both PRECISION (total digits, 0–38) and SCALE (digits after the decimal point, 0–PRECISION). From V4.2, high-precision mode supports up to 76 digits: PRECISION (0–76) and SCALE (0–PRECISION).

Up to 38 digits before and 38 digits after the decimal point. From V4.2, supports up to 76 digits with high precision.

DECIMAL(38, 10)

BOOLEAN

BOOL

1 byte

Boolean.

True / False

True

TEXT

—

Variable

Variable-length string. Prefer TEXT over VARCHAR(n) or CHAR(n) for maximum flexibility.

None

abcdefg

CHAR(n)

—

Fixed, up to n characters

Fixed-length character string. Storage size does not exceed 1 GB.

Up to n characters

abcd

VARCHAR(n)

—

Variable, up to n characters

Variable-length string with a character limit. Storage size does not exceed 1 GB.

Up to n characters

abcdefg

TIMESTAMP WITH TIME ZONE

TIMESTAMPTZ

8 bytes

Timestamp with time zone, stored with millisecond precision. If no time zone offset is specified, the system automatically adds the default time zone to the data.

4713 BC to 294276 AD

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

TIMESTAMP

—

8 bytes

Timestamp without time zone, stored with microsecond precision.

4713 BC to 5874897 AD

2020-01-01 01:01:01.123456

DATE

—

4 bytes

Date, with day granularity.

4713 BC to 5874897 AD

2004-10-19

TIME

—

8 bytes

Time of day without time zone, with microsecond precision.

00:00:00 to 24:00:00

12:00:00

TIMETZ

—

12 bytes

Time of day with time zone, with microsecond precision.

00:00:00 to 24:00:00

12:00:00+08

INTERVAL

—

16 bytes

Time interval.

-178,000,000 years to 178,000,000 years

interval '1 year'

JSON

—

Variable

JSON type. For details, see .

None

None

JSONB

—

Variable

Binary JSON type. For details, see .

None

None

BYTEA

—

Variable

Variable-length binary string. Storage size does not exceed 1 GB. For details, see Binary Data Types.

None

None

BIT(n)

—

n bits

Fixed-length bit string. Storage size does not exceed 1 GB.

None

None

VARBIT(n)

—

Variable, up to n bits

Variable-length bit string. Storage size does not exceed 1 GB.

None

None

INET

—

Variable

IPv4 or IPv6 host address. For details, see Network address types.

None

192.168.100.128/25

MONEY

—

8 bytes

Currency amount with fixed fractional precision. For details, see Currency types.

-92,233,720,368,547,758.08 to +92,233,720,368,547,758.07

12.34 USD

OID

—

4 bytes

Numeric object identifier.

None

1024

UUID

—

16 bytes

Universally unique identifier (UUID), fixed-length 128-bit value. uuid-ossp algorithms are not currently supported.

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

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

SERIAL

—

Variable

Auto-increment sequence. For details, see Auto-increment sequence.

None

None

RoaringBitmap

—

Variable

Efficient INT array that supports constant array bitmap operations. For details, see RoaringBitmap functions.

None

None

RoaringBitmap64

—

Variable

Efficient BIGINT array that supports constant array bitmap operations. For details, see RoaringBitmap functions.

None

None

GEOMETRY and GEOGRAPHY

—

Variable. For details, see PostGIS-based geographic analysis.

PostGIS is a spatial extension of PostgreSQL that provides spatial objects, spatial indexes, spatial operation functions, and spatial operators. Performance is improved from V1.3.

None

None

Version availability: Types not available in all versions were added in specific releases:

Type

Added in

DATE, TIMESTAMP, CHAR(n), VARCHAR(n), SERIAL

Hologres V0.8

SMALLINT, JSON, JSONB, BYTEA, BIT(n), VARBIT(n), TIMETZ, TIME, INET, MONEY, OID, UUID

Hologres V0.9

RoaringBitmap

Hologres V0.10

RoaringBitmap64

Hologres V3.1

All remaining types

All Hologres versions

Examples

The following examples demonstrate common DDL and DML operations for selected types.

DECIMAL256 usage and examples

Usage limits:

  • DECIMAL256 is supported only in Hologres V4.2 and later.

  • DECIMAL256 cannot be used as a distribution key, clustering key, partition key, segment key, bitmap index, or dictionary encoding column.

  • Supported in CREATE TABLE and CREATE DYNAMIC TABLE (full and incremental refresh) statements.

  • Supported in row-oriented, column-oriented, and row-column hybrid storage.

  • Supported functions: arithmetic operations, comparison operations, SUM, AVG, MIN, MAX, CAST, and TRY_CAST.

Example:

-- Create a table with DECIMAL256 columns
CREATE TABLE financial_transactions (
    id INT PRIMARY KEY,
    -- Standard DECIMAL(38,19): supports up to 38 digits
    amount_standard DECIMAL(38, 19),
    -- DECIMAL256(76,38): supports up to 76 digits
    amount_high_precision DECIMAL(76, 38),
    description TEXT
);
-- Insert high-precision values
INSERT INTO financial_transactions VALUES
(1,
 1234567890123456789.1234567890123456789,  -- 38-digit precision
 '12345678901234567890123456789012345678.12345678901234567890123456789012345678',  -- 76-digit precision
 'High-precision transaction amount'
);

-- Query the data
SELECT * FROM financial_transactions;
 id |             amount_standard             |                             amount_high_precision                             |           description
----+-----------------------------------------+-------------------------------------------------------------------------------+----------------------------------
  1 | 1234567890123456789.1234567890123456789 | 12345678901234567890123456789012345678.12345678901234567890123456789012345678 | High-precision transaction amount
(1 row)

Time type examples

TIMESTAMPTZ, 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_column 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_column
------------------------+-------------+----------------+----------------------+----------------
 2004-10-19 08:08:08+08 | 2004-10-19  | 123.4560000000 | abcd                 | a
(1 row)

BIT, VARBIT, and BYTEA:

-- BIT and VARBIT
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 (escape output format)
SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T

RESET bytea_output;  -- 'hex' is the default

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

Array types

Hologres supports one-dimensional arrays only. The following table shows the supported array types with declaration and usage syntax.

Type

Description

Declare in DDL

Insert with ARRAY keyword

Insert with {} syntax

Query single element

Query a range

int4[]

One-dimensional integer array

col int4[]

ARRAY[1, 2, 3, 4]

'{1, 2, 3, 4}'

col[3]

col[1:2]

int8[]

One-dimensional bigint array

col int8[]

ARRAY[1, 2, 3, 4]

'{1, 2, 3, 4}'

col[3]

col[1:2]

float4[]

One-dimensional real array

col float4[]

ARRAY[1.0, 2.0]

'{1.0, 2.0}'

col[1]

col[1:2]

float8[]

One-dimensional double precision array

col float8[]

ARRAY[1.0, 2.0, 3.0]

'{1.0, 2.0, 3.0}'

col[1]

col[1:2]

boolean[]

One-dimensional boolean array

col boolean[]

ARRAY[true, true, false]

'{true, true, false}'

col[1]

col[1:2]

text[]

One-dimensional text array

col text[]

ARRAY['foo1', 'foo2', 'foo3']

'{"foo1", "foo2", "foo3"}'

col[1]

col[1:2]

Full example:

-- Declare
CREATE TABLE array_example(
  int4_array    int4[],
  int8_array    int8[],
  float4_array  float4[],
  float8_array  float8[],
  boolean_array boolean[],
  text_array    text[]
);

-- Insert using 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']
);

-- Insert using the {} syntax
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 a single element (1-based index)
SELECT int4_array[3] FROM array_example;

-- Query a range
SELECT int4_array[1:2] FROM array_example;

Data type mapping between MaxCompute and Hologres

The following table shows how MaxCompute types map to Hologres types when creating a MaxCompute foreign table.

If a MaxCompute table contains fields of unsupported types, you can still query supported fields, provided the query does not access the unsupported fields.

MaxCompute type

Hologres type

Available since

Notes

JSON

JSONB

Hologres V4.1

—

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

MaxCompute DATETIME uses China Standard Time (UTC+8). Range: January 1, 0000 to December 31, 9999, with millisecond precision.

DECIMAL

NUMERIC

All versions

Default precision is (38,18) when not specified. When you use IMPORT FOREIGN SCHEMA to create a table, the system automatically converts the precision.

TIMESTAMP

TIMESTAMP WITH TIME ZONE

Hologres V0.8

MaxCompute TIMESTAMP range: 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999 (nanosecond precision). Hologres TIMESTAMPTZ uses millisecond precision; precision is converted automatically during reads.

CHAR(n)

CHAR(n) (default); TEXT (optional)

Hologres V0.8

Fixed-length character type; n is the length, maximum 255. Padded with spaces if shorter than n. To map to TEXT, set hg_enable_convert_type_for_foreign_table = true and change the field type to TEXT when creating the table.

VARCHAR(n)

VARCHAR(n) (default); TEXT (optional)

Hologres V0.8

Variable-length character type; n ranges from 1 to 65,535. To map to TEXT, set hg_enable_convert_type_for_foreign_table = true and change the field type to TEXT when creating the table.

DATE

DATE

Hologres V0.8

—

SMALLINT

INT2 (default); INT8 (optional)

All versions (INT4 in V0.8, INT2 in V0.9)

To map to INT8, set hg_enable_convert_type_for_foreign_table = true and change the field type to INT8 when creating the table.

TINYINT

INT2 (default); INT8 (optional)

All versions (INT4 in V0.8, INT2 in V0.9)

To map to INT8, set hg_enable_convert_type_for_foreign_table = true and change the field type to INT8 when creating the table.

CHAR (no length)

Not supported

Not supported

—

ARRAY\<INT\>

INT4[]

Hologres V0.8

—

ARRAY\<BIGINT\>

INT8[]

Hologres V0.8

—

ARRAY\<FLOAT\>

FLOAT4[]

Hologres V0.8

—

ARRAY\<DOUBLE\>

FLOAT8[]

Hologres V0.8

—

ARRAY\<BOOLEAN\>

BOOLEAN[]

Hologres V0.8

—

ARRAY\<STRING\>

TEXT[]

Hologres V0.8

—

BINARY

BYTEA

Hologres V0.9

—

ARRAY\<TINYINT\>

Not supported

Not supported

—

ARRAY\<SMALLINT\>

Not supported

Not supported

—

Data type mapping between Blink/Flink and Hologres

The following table shows how Flink types map to Hologres types.

Binlog source tables support only a subset of data types. For more information, see Consume Hologres Binlog in real time with Flink/Blink.

Flink type

Hologres type

Supported Hologres version

Supported Flink 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

All versions

All versions

DATE

DATE

Hologres V0.8

All versions

TIMESTAMP

TIMESTAMP WITH TIME ZONE, TIMESTAMPTZ

All versions

All versions

FLOAT

FLOAT4, REAL

All versions

All versions

TINYINT

SMALLINT

All versions

Sink: VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported.

SMALLINT

SMALLINT

All versions

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

TIME

TIME and TIMETZ

All versions. Starting from Hologres V2.1.24, Fixed Plan supports TIME and TIMETZ.

Sink: VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported.

Note

Fixed Plan does not support the TIME type; avoid this type where possible. For details, see Fixed Plan.

VARCHAR

JSONB

Hologres V0.10

Sink: VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported.

VARCHAR

JSON

Hologres V0.9

Sink: VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported.

BYTES

RoaringBitmap

Hologres V0.10

Sink: VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: VVR-4.0.12-Flink-1.13 and later. RPC mode is not supported.

VARCHAR

GEOMETRY and GEOGRAPHY

All versions. Starting from Hologres V2.1, Fixed Plan supports writing GEOMETRY and GEOGRAPHY data.

Sink: VVR-4.0.13-Flink-1.13 and later. RPC mode is not supported. Source: Not supported. Dimension: Not supported.

ARRAY\<INT\>

int4[]

Hologres V0.8

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

ARRAY\<BIGINT\>

int8[]

Hologres V0.8

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

ARRAY\<FLOAT\>

float4[]

Hologres V0.8

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

ARRAY\<DOUBLE\>

float8[]

Hologres V0.8

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

ARRAY\<BOOLEAN\>

boolean[]

Hologres V0.8

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

TEXT[]

Hologres V0.8

Sink: All versions. Source: VVR-6.0.3-Flink-1.15 and later. RPC mode is not supported. Dimension: All versions.

ARRAY\<VARCHAR\>

Hologres V0.8

Sink: All versions. Source: Hologres V4.0.19 and later, with Flink engine VVR-11.6-JDK11-Flink-1.20. Dimension: All versions.

CHAR

Not supported

Not supported

Not supported

BINARY

Not supported

Not supported

Not supported

When using CTAS to synchronize data to Hologres, DECIMAL primary keys are mapped to TEXT, while non-primary key DECIMAL fields are mapped to DECIMAL. For more information, see Why does the primary key of a MySQL table with bigint unsigned become decimal when registering the Flink Catalog, but become text after synchronizing to Hologres using CTAS?

Data type mapping between MySQL and Hologres

For a migration walkthrough, see Migrate MySQL to Hologres.

MySQL type

Hologres 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 mapping between DLF and Hologres

DLF type

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

CHAR(n)

ARRAY\<type\>

ARRAY\<hologres_data_type\>. Supported element types: INT, BIGINT, FLOAT, BOOLEAN, DOUBLE, STRING

Data type mapping between Hive and Hologres

Hive type

Hologres 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\>. Supported element types: INT, BIGINT, FLOAT, BOOLEAN, DOUBLE PRECISION, STRING

Data type mapping between Hudi and Hologres

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

Hudi type

Hologres 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\>. Supported element types: INT, BIGINT, FLOAT, BOOLEAN, DOUBLE PRECISION, STRING

Data type mapping between Delta Lake and Hologres

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

Delta Lake type

Hologres 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\>. Supported element types: INT, BIGINT, FLOAT, BOOLEAN, DOUBLE PRECISION, STRING

Data type mapping between Paimon and Hologres

Paimon type

Hologres 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

BLOB

BYTES (supported from V4.2)

ARRAY

ARRAY\<hologres_data_type\>. Supported element types: INT, BIGINT, FLOAT, BOOLEAN, DOUBLE PRECISION, STRING

Data type mapping between Iceberg and Hologres

Iceberg type

Hologres type

BOOLEAN

BOOLEAN

INT

INTEGER

LONG

BIGINT

FLOAT

REAL

DOUBLE

DOUBLE PRECISION

DECIMAL(P,S)

NUMERIC(P,S)

DATE

DATE

TIME

TEXT (Spark does not support the TIME type. Flink's TIME type becomes STRING when written to DLF.)

TIMESTAMP

TIMESTAMP WITHOUT TIME ZONE

TIMESTAMPTZ

Not supported

STRING

TEXT

UUID

Not supported (Flink and Spark cannot write this type.)

FIXED(L)

BYTEA

BINARY

BYTEA

LIST

ARRAY\<hologres_data_type\>. Supported element types: INT, BIGINT, FLOAT, BOOLEAN, DOUBLE PRECISION, STRING

STRUCT

Not supported

MAP

Not supported

Previous：Data typesNext：PostgreSQL SERIAL