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
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 |
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.
|
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. |
|
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 |
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 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
- int4[]
- int8[]
- float4[]
- float8[]
- boolean[]
- text[]
- 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"}');
- Use the ARRAY keyword.
- 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;
- Query an element in an array.
Data type mappings between MaxCompute and Hologres when you create a foreign table
MaxCompute data type | Hologres data type | Supported version | Description |
---|---|---|---|
|
TEXT | All Hologres versions | None |
BIGINT | INT8 | All Hologres versions | None |
INT |
|
All Hologres versions | None |
FLOAT |
|
All Hologres versions | None |
DOUBLE |
|
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 |
|
CHAR(n) | By default, CHAR(n) is used.
Hologres allows you to map CHAR(n) in MaxCompute to TEXT. You must set the |
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 |
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 |
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 |
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 |
Data type mappings between Realtime Compute for Apache Flink and Hologres
Realtime Compute data type | Hologres data type | Supported version |
---|---|---|
VARCHAR | TEXT | All Hologres versions |
BIGINT | INT8 | All Hologres versions |
INT |
|
All Hologres versions |
SMALLINT | SMALLINT | All Hologres versions |
TINYINT | SMALLINT | All Hologres versions |
FLOAT |
|
All Hologres versions |
DOUBLE |
|
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 |
|
|
DATE | DATE |
DATETIME | TIMESTAMP [WITHOUT TIME ZONE] |
|
|
DOUBLE | DOUBLE PRECISION |
FLOAT | REAL |
|
|
MEDIUMINT | INTEGER |
NUMERIC(p,s) | NUMERIC(p,s) |
SMALLINT | SMALLINT |
|
BYTEA |
TINYINT | SMALLINT |
|
TEXT |
TIME | TIME [WITHOUT TIME ZONE] |
TIMESTAMP | TIMESTAMP [WITH TIME ZONE] |
|
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:
|
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:
|
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:
|
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:
|