Data Types

Hologres is compatible with PostgresSQL in data types. Currently Hologres supports a subset of data types defined in PostgresSQL.

Data Types

The following table lists all supported types:

Name Alias Supported Length in bytes Value range Description
integer int, int4 Yes 4 -2147483648 to +2147483647 signed four bytes integer
bigint int8 Yes 8 -9223372036854775808 to +9223372036854775807 signed eight bytes integer
boolean bool Yes 1 True / False logical boolean
real float4 Yes 4 6 decimal digits precision single precision floating-point number
double precision float8 Yes 8 15 decimal digits precision double precision floating-point number
text varchar Yes variable - variable length character string
timestamp with time zone timestamptz Yes 8 4713 BC to 294276 AD date and time, including timezone, e.g '2004-10-19 10:23:54+02'
decimal numeric Yes variable up to 38 digits before decimal point; up to 38 digits after the decimal point exact numeric with selected precision

For timestamp with time zone type, SQL standard using the number after symbol ‘+’ or ‘-‘ as time zone shift. If no time zone is provided, a default one is used.

Note: Defference between Hologres and PostgresSQL for decimal type:

  • Hologres supports precision from 0 to38, scale from 0 toprecision
  • decimal type muse provide precision and scale, no default value

Below is an example for timestamptz, date and decimal:

CREATE TABLE test_data_type (
	tswtz_column timestamp WITH TIME ZONE,
	date_column date,
	decimal_column decimal(38, 10)
);

INSERT INTO test_data_type
VALUES ('2004-10-19 08:08:08', '2004-10-19', 123.456);

SELECT * FROM test_data_type;

Array Types

Hologres currently only support one dimensional array types listed below:

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

Declaration of array types

create table array_example(
int4_array int4[],
int8_array int8[],
float4_array float4[],
float8_array float8[],
boolean_array boolean[],
text_array text[]);

Array value input

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

using {} 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"}');

Accessing Arrays

access single element of an array

SELECT int4_array[3] FROM array_example;

access a slice of an array

SELECT int4_array[1:2] FROM array_example;