Hologres:Data types overview

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.

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

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 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.

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.

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.

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.

Data type mapping between DLF and Hologres

Data type mapping between Hive and Hologres

Data type mapping between Hudi and Hologres

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

Data type mapping between Delta Lake and Hologres

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

Data type mapping between Paimon and Hologres

Data type mapping between Iceberg and Hologres