AnalyticDB:ARRAY type

Declare an array column

Declare an array type by appending [] to the element data type name. For example:

CREATE TABLE sal_emp (
    id              int,
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
) DISTRIBUTED BY(id);

Write array values

Use INSERT to add rows with array values. Array literals use curly-brace syntax: '{value1, value2, ...}'.

INSERT INTO sal_emp
    VALUES (1,
    'Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES (2,
    'Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Verify the inserted data:

SELECT * FROM sal_emp;

 id | name  |      pay_by_quarter       |                 schedule
----+-------+---------------------------+-------------------------------------------
  2 | Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
  1 | Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
(2 rows)

Access array elements and slices

Arrays use 1-based indexing. For an array with n elements, valid subscripts range from array[1] to array[n].

To access a slice, write lower-bound:upper-bound for each dimension.

Access individual elements

Query employees whose salary changed between the first and second quarter:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

name
-------
 Carol
(1 row)

Query the third-quarter salary for all employees:

SELECT pay_by_quarter[3] FROM sal_emp;

pay_by_quarter
----------------
          10000
          25000
(2 rows)

Access a slice

Query the first item on Bill's schedule for the first two days of the week:

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

schedule
------------------------
 {{meeting},{training}}
(1 row)

Modify arrays

AnalyticDB for PostgreSQL supports three UPDATE patterns for arrays:

Replace the entire array:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

Update a single element:

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

Update a slice:

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

Concatenate arrays using ||

The concatenation operator (||) builds new array values from existing ones. It accepts element-to-array, array-to-element, and array-to-array combinations:

SELECT ARRAY[1,2] || ARRAY[3,4];

?column?
-----------
 {1,2,3,4}
(1 row)

|| also accepts an N-dimensional array combined with an (N+1)-dimensional array:

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];

?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

Use array functions to concatenate

array_prepend and array_append support one-dimensional arrays only. array_cat supports multidimensional arrays. The || operator is preferred over these functions in most cases.

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

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

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

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

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

Search arrays

Use the ANY, ALL, and && operators to search for values across array elements.

Find rows where the array contains a specific value:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

Find rows where all elements equal a specific value:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Find rows where the array overlaps with another array:

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

Tip: Frequent searches for individual array elements can signal a schema design issue. If this is a core access pattern, consider a separate table with one row per element—it's easier to index and scales better for large data volumes.

Use a GIN index to accelerate array search

For large arrays, using the @> operator without an index triggers a sequential scan, which degrades as array size grows. A GIN index significantly reduces query time for containment searches.

Setup: Create a table with 1,000 rows, each containing 1,000,000 array elements:

-- Create a table with an integer array column
CREATE TABLE multivalue (
    id      int,
    values  int[]
) DISTRIBUTED BY(id);

-- Insert 1,000 rows; each row's values column holds 1,000,000 elements
INSERT INTO multivalue SELECT g, ARRAY(SELECT generate_series(1, 1000000)) FROM generate_series(1, 1000) as g;

Without an index, the query uses a sequential scan and takes ~3,483 ms:

EXPLAIN ANALYZE SELECT id FROM multivalue WHERE values @> ARRAY[800000];

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.00 rows=1 width=4) (actual time=711.216..3478.225 rows=1000 loops=1)
   ->  Seq Scan on multivalue  (cost=0.00..1.00 rows=1 width=4) (actual time=10.420..2629.403 rows=508 loops=1)
         Filter: ("values" @> '{800000}'::integer[])
 Planning time: 0.080 ms
   (slice0)    Executor memory: 39K bytes.
   (slice1)    Executor memory: 12733K bytes avg x 2 workers, 12733K bytes max (seg0).
 Memory used:  1048576kB
 Optimizer: Postgres query optimizer
 Execution time: 3483.157 ms
(9 rows)

Time: 3483.667 ms

After creating a GIN index, the same query uses a bitmap index scan and completes in ~9.7 ms—a ~360x improvement:

CREATE INDEX idx_values ON multivalue USING GIN (values);

EXPLAIN ANALYZE SELECT id FROM multivalue WHERE values @> ARRAY[800000];

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=12.04..14.10 rows=5 width=4) (actual time=3.727..4.127 rows=1000 loops=1)
   ->  Bitmap Heap Scan on multivalue  (cost=12.04..14.10 rows=3 width=4) (actual time=1.826..1.872 rows=508 loops=1)
         Recheck Cond: ("values" @> '{800000}'::integer[])
         ->  Bitmap Index Scan on idx_values  (cost=0.00..12.04 rows=3 width=0) (actual time=1.462..1.462 rows=508 loops=1)
               Index Cond: ("values" @> '{800000}'::integer[])
 Planning time: 0.155 ms
   (slice0)    Executor memory: 49K bytes.
   (slice1)    Executor memory: 331K bytes avg x 2 workers, 331K bytes max (seg0).  Work_mem: 9K bytes max.
 Memory used:  1048576kB
 Optimizer: Postgres query optimizer
 Execution time: 9.138 ms
(11 rows)

Time: 9.731 ms

GIN indexes improve search performance but increase write latency (the index is updated on every write) and use additional storage. Weigh search frequency, write throughput requirements, and storage costs before adding a GIN index.

Array functions

The following functions are supported for array operations.

string_to_array behavior:

• If the delimiter is NULL, each character in the input becomes a separate element.

• If the delimiter is an empty string, the entire input is returned as a one-element array.

• Otherwise, the input is split at each occurrence of the delimiter.

• If the null string parameter is omitted or NULL, no substrings are replaced by NULL.

array_to_string behavior:

• If the null string parameter is omitted or NULL, NULL elements are skipped and not included in the output.

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

array_dims
------------
 [1:2][1:2]
(1 row)

SELECT array_upper(schedule, 1), array_lower(schedule, 1) FROM sal_emp WHERE name = 'Carol';

array_upper | array_lower
-------------+-------------
           2 |           1
(1 row)

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

array_length
--------------
            2
(1 row)

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

cardinality
-------------
           4
(1 row)

What's next

For the full PostgreSQL array reference, see PostgreSQL documentation.