Hologres:Array functions

ARRAY_TO_STRING

array_to_string(anyarray, text[, text]) → TEXT

Concatenates array elements using a specified separator. The optional third argument replaces NULL elements; if omitted, NULLs are skipped.

Parameters

Example

-- Result: 1,2,3
SELECT array_to_string(ARRAY[1, 2, 3], ',');

ARRAY_AGG

array_agg(anyelement) → ARRAY

Aggregates column values from multiple rows into a single array. Two syntax forms are supported.

Syntax 1: Basic aggregation

array_agg(anyelement)

Syntax 2: Aggregation with ordering and filtering

array_agg(expression [order_by_clause]) [FILTER (WHERE filter_clause)]

Parameters

Usage notes

• Supported in Hologres V1.3 and later: DECIMAL, DATE, TIMESTAMP, TIMESTAMPTZ.

• Not supported: JSON, JSONB, TIMETZ, INTERVAL, INET, OID, UUID, ARRAY.

• The FILTER clause requires Hologres V1.3 or later. To upgrade your instance, see Instance upgradation or How do I get more online support?.

Examples

Basic aggregation:

CREATE TABLE test_array_agg_int (
    c1 int
);

INSERT INTO test_array_agg_int
    VALUES (1), (2);

SELECT array_agg(c1)
FROM test_array_agg_int;

Result:

 array_agg
-----------
 {2,1}
(1 row)

Aggregation with FILTER (requires Hologres V1.3+):

SELECT array_agg(c1) FILTER (WHERE c1 > 1)
FROM test_array_agg_int;

Result:

 array_agg
-----------
 {2}
(1 row)

ARRAY_APPEND

array_append(anyarray, anyelement) → ARRAY

Appends an element to the end of an array.

Parameters

Example

-- Result: {1,2,3}
SELECT array_append(ARRAY[1,2], 3);

ARRAY_CAT

array_cat(anyarray, anyarray) → ARRAY

Concatenates two arrays of the same element type.

Parameters

Example

-- Result: {1,2,3,4,5}
SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);

ARRAY_NDIMS

array_ndims(anyarray) → INT

Returns the number of dimensions of an array.

Parameters

Example

-- Result: 2
SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);

ARRAY_DIMS

array_dims(anyarray) → TEXT

Returns the dimension bounds of an array as a text string, showing the lower and upper bound of each dimension.

Parameters

Example

-- Result: [1:2][1:3]
SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);

ARRAY_LENGTH

array_length(anyarray, int) → INT

Returns the number of elements in the specified dimension of an array. Dimensions are numbered starting from 1.

Parameters

Example

-- Result: 3
SELECT array_length(ARRAY[1,2,3], 1);

ARRAY_LOWER

array_lower(anyarray, int) → INT

Returns the lower bound of the specified array dimension. For standard arrays, this is 1. For arrays with a custom lower bound (such as '[0:2]={1,2,3}'::int[]), the actual lower bound is returned. Dimensions are numbered starting from 1.

Parameters

Example

-- Result: 0
SELECT array_lower('[0:2]={1,2,3}'::int[], 1);

ARRAY_POSITIONS

array_positions(anyarray, anyelement) → ARRAY

Returns the subscripts of a specified element in a one-dimensional array.

Parameters

Example

-- Result: {1,2,4}
SELECT array_positions(ARRAY['A','A','B','A'], 'A');

ARRAY_PREPEND

array_prepend(anyelement, anyarray) → ARRAY

Adds an element to the beginning of an array.

Parameters

Example

-- Result: {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);

ARRAY_REMOVE

array_remove(anyarray, anyelement) → ARRAY

Removes all elements equal to the specified value from a one-dimensional array.

Parameters

Example

-- Result: {1,3}
SELECT array_remove(ARRAY[1,2,3,2], 2);

ARRAY_SORT

array_sort(anyarray) → ARRAY

Sorts the elements of an array in ascending order.

Usage notes

Parameters

Example

-- Result: {1,1,2,3}
SELECT array_sort(ARRAY[1,3,2,1]);

ARRAY_UPPER

array_upper(anyarray, int) → INT

Returns the upper bound of the specified array dimension. For a standard 1-dimensional array, this equals the number of elements. Dimensions are numbered starting from 1.

Parameters

Example

-- Result: 4
SELECT array_upper(ARRAY[1,8,3,7], 1);

UNNEST

unnest(anyarray) → setof TEXT

Expands each element of an array into a separate row. Use this function in the FROM clause or as a set-returning function.

Parameters

Example

SELECT unnest(ARRAY[1,2]);

Result:

unnest
------
1
2
(2 rows)

ARRAY_MAX

array_max(array) → INT

Returns the maximum element value in an array. NULL values are skipped during the calculation. Requires Hologres V1.3.19 or later.

array_max does not support constant queries—use a table column as input.

Parameters

Example

CREATE TABLE test_array_max_int (
    c1 int[]
);

INSERT INTO test_array_max_int
    VALUES (NULL), (ARRAY[-2, NULL, -3, -12, -7]);

SELECT c1, array_max(c1)
FROM test_array_max_int;

Result:

        c1        | array_max
------------------+-----------
 \N               | \N
 {-2,0,-3,-12,-7} |         0
(2 rows)

ARRAY_MIN

array_min(array) → INT

Returns the minimum element value in an array. Requires Hologres V1.3.19 or later.

array_min does not support constant queries—use a table column as input.

Parameters

Example

CREATE TABLE test_array_min_text (
    c1 text[]
);

INSERT INTO test_array_min_text
    VALUES (NULL), (ARRAY['hello', 'holo', 'blackhole', 'array']);

SELECT c1, array_min(c1)
FROM test_array_min_text;

Result:

              c1              | array_min
------------------------------+-----------
 \N                           | \N
 {hello,holo,blackhole,array} | array
(2 rows)

ARRAY_CONTAINS

array_contains(array, target_value) → BOOLEAN

Checks whether an array contains a specified value. Returns true if found, false if not. Requires Hologres V1.3.19 or later.

array_contains does not support constant queries—use a table column as input.

Parameters

Example

CREATE TABLE test_array_contains_text (
    c1 text[],
    c2 text
);

INSERT INTO test_array_contains_text
    VALUES (ARRAY[NULL, 'cs', 'holo', 'sql', 'a', NULL, ''], 'holo'),
           (ARRAY['holo', 'array', 'FE', 'l', NULL, ''], 'function');

SELECT c1, c2, array_contains(c1, c2)
FROM test_array_contains_text;

Result:

            c1            |    c2    | array_contains
--------------------------+----------+----------------
 {holo,array,FE,l,"",""}  | function | f
 {"",cs,holo,sql,a,"",""} | holo     | t
(2 rows)

ARRAY_EXCEPT

array_except(array1, array2) → ARRAY

Returns the elements in array1 that are not in array2. Requires Hologres V1.3.19 or later.

array_except does not support constant queries—use table columns as input.

Parameters

Example

CREATE TABLE test_array_except_text (
    c1 text[],
    c2 text[]
);

INSERT INTO test_array_except_text
    VALUES (ARRAY['o', 'y', 'l', 'l', NULL, ''], NULL),
           (ARRAY['holo', 'hello', 'hello', 'SQL', '', 'blackhole'], ARRAY['holo', 'SQL', NULL, 'kk']);

SELECT c1, c2, array_except(c1, c2)
FROM test_array_except_text;

Result:

                 c1                  |        c2        |   array_except
-------------------------------------+------------------+-------------------
 {o,y,l,l,"",""}                     |                  | {o,l,y,""}
 {holo,hello,hello,SQL,"",blackhole} | {holo,SQL,"",kk} | {blackhole,hello}
(2 rows)

ARRAY_DISTINCT

array_distinct(array) → ARRAY

Removes duplicate elements from an array. Requires Hologres V1.3.19 or later.

array_distinct does not support constant queries—use a table column as input.

Parameters

Example

CREATE TABLE test_array_distinct_text (
    c1 text[]
);

INSERT INTO test_array_distinct_text
    VALUES (ARRAY['holo', 'hello', 'holo', 'SQL', 'SQL']),
           (ARRAY[]::text[]);

SELECT c1, array_distinct(c1)
FROM test_array_distinct_text;

Result:

            c1             |  array_distinct
---------------------------+------------------
 {holo,hello,holo,SQL,SQL} | {SQL,hello,holo}
 {}                        | {NULL}
(2 rows)

ARRAY_UNION

array_union(array1, array2) → ARRAY

Merges two arrays into a new array and removes duplicate elements. Requires Hologres V1.3.19 or later.

array_union does not support constant queries—use table columns as input.

Parameters

Example

CREATE TABLE test_array_union_int (
    c1 int[],
    c2 int[]
);

INSERT INTO test_array_union_int
    VALUES (NULL, ARRAY[2, -3, 2, 7]),
           (ARRAY[2, 7, -3, 2, 7], ARRAY[12, 9, 8, 7]);

SELECT c1, c2, array_union(c1, c2)
FROM test_array_union_int;

Result:

      c1      |     c2     |   array_union
--------------+------------+-----------------
 \N           | {2,-3,2,7} | {2,7,-3}
 {2,7,-3,2,7} | {12,9,8,7} | {9,2,7,8,12,-3}
(2 rows)

REGEXP_MATCH

regexp_match(str TEXT, pattern TEXT) → ARRAY

Matches a string against a regular expression and returns an array of captured substrings.

Parameters

Example

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');

Result:

 regexp_match
--------------
 {bar,beque}

REGEXP_SPLIT_TO_ARRAY

regexp_split_to_array(str TEXT, pattern TEXT) → ARRAY

Splits a string by a regular expression delimiter and returns the parts as an array.

Parameters

Example

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Ava', 'singing, dancing'),
           ('Bob', 'playing football, running, painting'),
           ('Jack', 'arranging flowers, writing calligraphy, playing the piano, sleeping');

SELECT name, regexp_split_to_array(intrests, ',')
FROM interests_test;

Result:

name | regexp_split_to_array
----------------------------
Ava  | {singing, dancing}
Bob  | {playing football, running, painting}
Jack | {arranging flowers, writing calligraphy, playing the piano, sleeping}