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 larger 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 | Variable precision, imprecise.
Note In PostgreSQL, the REAL data type without the precision set is equivalent to DOUBLE
PRECISION (FLOAT8).
|
A number that has 6 decimal digits of precision. | 123.123 |
DOUBLE PRECISION (alias: FLOAT8) | All Hologres versions | 8 bytes | Variable precision, imprecise. | A number that has 15 decimal digits of precision. | 123.123456789123 |
TEXT (alias: VARCHAR) | All Hologres versions | Variable length | A character string with a variable length. | N/A | 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 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 time zone indicated by the
timezone parameter is used. Then, the time zone is converted to a UTC time zone by
using the offset of the timezone parameter.
|
4713 BC to 294276 AD | 2004-10-19 10:23:54+02 |
DECIMAL (alias: NUMERIC) | All Hologres versions | Variable length | The precision and scale must be specified.
|
Maximum precision and scale: 38. | DECIMAL(38, 10) |
DATE | Hologres V0.8 | 4 bytes | Unit: day. | 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. | N/A | N/A | N/A |
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 and JSONB data types. | N/A | N/A | N/A |
BYTEA | Hologres V0.9 | The value is variable in 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. | N/A |
RoaringBitmap | Hologres V0.9 | The value is variable in length. For more information, see Roaring bitmap functions. | An efficient array of the INT data type that supports bitmap calculation of constant arrays. | N/A | N/A |
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. | N/A |
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. | N/A |
INTERVAL | All Hologres versions | 16 bytes | N/A | -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 IP address in a data domain. | N/A | 192.168.100.128/25 |
MONEY | Hologres V0.9 | 8 bytes. For more information, see Currency type. | Stores 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. | N/A | 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 Hologres version | Description |
---|---|---|---|
|
TEXT | All Hologres versions | N/A |
BIGINT | INT8 | All Hologres versions | N/A |
INT |
|
All Hologres versions | N/A |
FLOAT |
|
All Hologres versions | N/A |
DOUBLE |
|
All Hologres versions | N/A |
BOOLEAN | BOOL | All Hologres versions | N/A |
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 the millisecond. |
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 scale are automatically converted when you create a MaxCompute table by executing the IMPORT FOREIGN SCHEMA statement. |
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 value of n cannot be larger than 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 that cannot exceed n characters. Valid values of n: 1 to 65535. |
DATE | DATE | Hologres V0.8 | N/A |
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. | N/A |
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. | N/A |
CHAR | Not supported | Not supported | N/A |
ARRAY<INT> | INT4[] | Hologres V0.8 | N/A |
ARRAY<BIGINT> | INT8[] | Hologres V0.8 | N/A |
ARRAY<FLOAT> | FLOAT4[] | Hologres V0.8 | N/A |
ARRAY<DOUBLE> | FLOAT8[] | Hologres V0.8 | N/A |
ARRAY<BOOLEAN> | BOOLEAN[] | Hologres V0.8 | N/A |
ARRAY<STRING> | TEXT[] | Hologres V0.8 | N/A |
BINARY | BYTEA | Hologres V0.9 | N/A |
ARRAY<TINYINT> | Not supported | Not supported | N/A |
ARRAY<SMALLINT> | Not supported | Not supported | N/A |
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.
Realtime Compute data type | Hologres data type | Supported Hologres version |
---|---|---|
VARCHAR | TEXT | All Hologres versions |
BIGINT | INT8 | All Hologres versions |
INT |
|
All Hologres versions |
SMALLINT |
|
All Hologres versions |
TINYINT |
|
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 | Not supported | Not supported |
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 of engine version vvr-4.0.12-flink-1.13 or later |
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 |