(opens in a new tab)

PostgreSQL Array Functions: Complete Reference

September 18, 2026 by Chat2DB Team

PostgreSQL lets any column hold a variable-length, multidimensional array of a base type: text[], integer[], numeric[], uuid[], even arrays of composite types. Arrays are a pragmatic middle ground between a fully normalized child table and a JSONB blob: they keep type safety, they can be indexed with GIN, and they come with a large library of functions and operators. This reference walks through those functions in the order you usually need them: constructing arrays, inspecting them, reading elements, modifying them, converting to and from other types, searching, sorting, and aggregating. Every example runs against the same small table, so you can paste the statements into psql or a SQL client such as Chat2DB (opens in a new tab), which renders array columns directly in the result grid.

Sample table

All examples below use this products table with a text[] column for tags and a numeric[] column for a price history.

CREATE TABLE products (
  id      serial PRIMARY KEY,
  name    text NOT NULL,
  tags    text[]    NOT NULL DEFAULT '{}',
  prices  numeric[] NOT NULL DEFAULT '{}'
);
 
INSERT INTO products (name, tags, prices) VALUES
  ('Keyboard', ARRAY['hardware', 'input', 'usb'],  ARRAY[49.99, 44.99, 39.99]),
  ('Monitor',  ARRAY['hardware', 'display'],       ARRAY[299.00, 279.00]),
  ('Mouse',    '{hardware,input,wireless}',        '{19.99}'),
  ('License',  '{}',                               '{}');

Constructing arrays

ARRAY constructor and array literals

There are two syntaxes for writing an array value. The ARRAY[...] constructor takes ordinary expressions, so it is the right choice when elements are computed. The literal form '{...}' is a string that PostgreSQL parses into the column type; it is what you see in pg_dump output and what drivers usually send.

SELECT ARRAY[1, 2, 3]                 AS ints,
       ARRAY['a', 'b']::text[]        AS texts,
       '{1,2,3}'::int[]               AS literal,
       '{"has space","comma,inside"}'::text[] AS quoted,
       ARRAY[ARRAY[1, 2], ARRAY[3, 4]] AS matrix;

  ints   | texts | literal |          quoted           |    matrix
---------+-------+---------+---------------------------+---------------
 {1,2,3} | {a,b} | {1,2,3} | {"has space","comma,inside"} | {{1,2},{3,4}}

Inside a literal, elements containing commas, braces, spaces or quotes must be wrapped in double quotes. An empty array is written '{}' and must usually be cast, because PostgreSQL cannot infer its element type on its own.

array_agg: build an array from rows

array_agg is the aggregate that turns a set of rows into one array. Combine it with ORDER BY inside the aggregate to control element order.

SELECT array_agg(name ORDER BY name) AS product_names
FROM products;

          product_names
-----------------------------------
 {Keyboard,License,Monitor,Mouse}

string_to_array and array_fill

string_to_array(text, delimiter [, null_string]) splits a string. array_fill(value, dimensions) creates an array of a given shape filled with one value, which is handy for initializing counters or matrices.

SELECT string_to_array('red,green,,blue', ',')        AS with_empty,
       string_to_array('red,green,,blue', ',', '')    AS empty_as_null,
       array_fill(0, ARRAY[3])                         AS zeros,
       array_fill('n/a'::text, ARRAY[2, 2])            AS grid;

     with_empty      |    empty_as_null     |  zeros  |          grid
---------------------+----------------------+---------+-------------------------
 {red,green,"",blue} | {red,green,NULL,blue} | {0,0,0} | {{n/a,n/a},{n/a,n/a}}

Inspecting arrays

array_length and why it returns NULL for empty arrays

array_length(array, dimension) takes a mandatory dimension argument; for a normal one-dimensional array that is 1. The trap everyone hits once: an empty array has no dimensions at all, so array_length('{}'::text[], 1) returns NULL, not 0.

SELECT name,
       array_length(tags, 1)  AS tag_len,
       cardinality(tags)      AS tag_count
FROM products
ORDER BY id;

   name   | tag_len | tag_count
----------+---------+-----------
 Keyboard |       3 |         3
 Monitor  |       2 |         2
 Mouse    |       3 |         3
 License  |         |         0

If you need a count, prefer cardinality(array), which returns the total number of elements across all dimensions and returns 0 for an empty array. If you must use array_length, wrap it: coalesce(array_length(tags, 1), 0).

array_ndims, array_dims, array_lower, array_upper

SELECT array_ndims(ARRAY[[1,2,3],[4,5,6]])  AS ndims,
       array_dims(ARRAY[[1,2,3],[4,5,6]])   AS dims,
       array_lower(ARRAY[10,20,30], 1)      AS lower_bound,
       array_upper(ARRAY[10,20,30], 1)      AS upper_bound;

 ndims |    dims    | lower_bound | upper_bound
-------+------------+-------------+-------------
     2 | [1:2][1:3] |           1 |           3

array_dims returns a text description of every dimension's bounds. Bounds normally start at 1, but PostgreSQL allows arbitrary lower bounds (for example '[0:2]={a,b,c}'::text[]), which is why array_lower exists at all.

Accessing elements

Subscripts are 1-based

Unlike most programming languages, PostgreSQL arrays start at index 1. Reading an index that does not exist does not raise an error; it returns NULL.

SELECT name,
       tags[1]  AS first_tag,
       tags[99] AS missing,
       prices[cardinality(prices)] AS latest_price
FROM products
ORDER BY id;

   name   | first_tag | missing | latest_price
----------+-----------+---------+--------------
 Keyboard | hardware  |         |        39.99
 Monitor  | hardware  |         |       279.00
 Mouse    | hardware  |         |        19.99
 License  |           |         |

Slices

A slice uses lower:upper and returns an array, even when it selects a single element. Either bound can be omitted.

SELECT tags[1:2]  AS first_two,
       tags[2:]   AS from_second,
       tags[:1]   AS up_to_first
FROM products
WHERE name = 'Keyboard';

     first_two    | from_second |  up_to_first
------------------+-------------+--------------
 {hardware,input} | {input,usb} | {hardware}

Modifying arrays

Arrays are values, not mutable objects, so every "modification" function returns a new array. Use them in UPDATE ... SET col = function(col, ...).

array_append, array_prepend, array_cat and the concatenation operator

SELECT array_append(ARRAY[1,2], 3)          AS appended,
       array_prepend(0, ARRAY[1,2])         AS prepended,
       array_cat(ARRAY[1,2], ARRAY[3,4])    AS concatenated,
       ARRAY[1,2] || 3                      AS op_append,
       0 || ARRAY[1,2]                      AS op_prepend,
       ARRAY[1,2] || ARRAY[3,4]             AS op_cat;

 appended | prepended | concatenated | op_append | op_prepend |  op_cat
----------+-----------+--------------+-----------+------------+-----------
 {1,2,3}  | {0,1,2}   | {1,2,3,4}    | {1,2,3}   | {0,1,2}    | {1,2,3,4}

The double-pipe operator does all three jobs depending on its operand types. A typical write looks like this:

UPDATE products
SET tags = tags || 'sale',
    prices = array_append(prices, 34.99)
WHERE name = 'Keyboard';

array_remove and array_replace

array_remove(array, value) removes every element equal to the value (it only works on one-dimensional arrays). array_replace(array, old, new) swaps every match.

SELECT array_remove(ARRAY['a','b','a','c'], 'a')     AS removed,
       array_replace(ARRAY[1,2,1,3], 1, 100)         AS replaced,
       array_remove(ARRAY[1,NULL,2], NULL)           AS nulls_dropped;

 removed | replaced    | nulls_dropped
---------+-------------+---------------
 {b,c}   | {100,2,100,3} | {1,2}

array_position and array_positions

array_position returns the subscript of the first match (or NULL), and array_positions returns an array of every matching subscript. These are useful for conditional updates, such as "only append if not already present":

UPDATE products
SET tags = tags || 'usb'
WHERE name = 'Mouse'
  AND array_position(tags, 'usb') IS NULL;
 
SELECT array_position(ARRAY['a','b','c'], 'b')      AS pos,
       array_positions(ARRAY['a','b','a'], 'a')     AS all_pos;

 pos | all_pos
-----+---------
   2 | {1,3}

Updating a single element by subscript

You can also assign directly to a subscript or a slice:

UPDATE products SET prices[1] = 45.00 WHERE name = 'Keyboard';
UPDATE products SET tags[2:3] = '{typing,usb-c}' WHERE name = 'Keyboard';

Converting arrays

array_to_string with a NULL replacement

array_to_string(array, delimiter [, null_string]) joins elements into text. Without the third argument, NULL elements are silently skipped; pass a replacement string to make them visible.

SELECT array_to_string(ARRAY['a', NULL, 'c'], ', ')         AS skipped,
       array_to_string(ARRAY['a', NULL, 'c'], ', ', '-')    AS replaced,
       array_to_string(tags, ' | ')                          AS tag_line
FROM products
WHERE name = 'Monitor';

 skipped | replaced |     tag_line
---------+----------+--------------------
 a, c    | a, -, c  | hardware | display

unnest, with and without ordinality

unnest is the inverse of array_agg: it expands one array into a set of rows. Add WITH ORDINALITY to get the original position as an extra column, which is essential when you need to preserve or reason about order.

SELECT p.name, t.tag, t.pos
FROM products p
CROSS JOIN LATERAL unnest(p.tags) WITH ORDINALITY AS t(tag, pos)
WHERE p.name = 'Keyboard';

   name   |   tag    | pos
----------+----------+-----
 Keyboard | hardware |   1
 Keyboard | input    |   2
 Keyboard | usb      |   3

Note that a product with an empty array produces zero rows from a CROSS JOIN LATERAL unnest(...). Use LEFT JOIN LATERAL ... ON true if you need to keep those rows.

array_to_json and to_jsonb

When an API consumer wants JSON, convert the array in SQL rather than in application code:

SELECT array_to_json(tags)  AS json_tags,
       to_jsonb(prices)     AS jsonb_prices
FROM products
WHERE name = 'Keyboard';

           json_tags           |      jsonb_prices
-------------------------------+------------------------
 ["hardware","input","usb"]    | [49.99, 44.99, 39.99]

Going the other way, ARRAY(SELECT jsonb_array_elements_text('["a","b"]')) turns a JSON array back into text[].

Searching and filtering

= ANY, containment and overlap operators

-- rows whose tags include 'input'
SELECT name FROM products WHERE 'input' = ANY (tags);
 
-- rows whose tags contain ALL of the listed values
SELECT name FROM products WHERE tags @> ARRAY['hardware', 'input'];
 
-- rows whose tags are a subset of the listed values
SELECT name FROM products WHERE tags <@ ARRAY['hardware', 'display', 'input', 'usb'];
 
-- rows whose tags share at least one value with the list
SELECT name FROM products WHERE tags && ARRAY['wireless', 'display'];

@> reads as "contains", <@ as "is contained by", and && as "overlaps". = ANY and = ALL compare a scalar against every element and are convenient, but they cannot use a GIN index.

Adding a GIN index

For tables with many rows, index the array column with GIN. The default array_ops operator class supports @>, <@, && and =.

CREATE INDEX products_tags_gin ON products USING GIN (tags);
 
EXPLAIN (COSTS OFF)
SELECT name FROM products WHERE tags @> ARRAY['usb'];

If you have a query written as 'usb' = ANY (tags), rewrite it to tags @> ARRAY['usb'] so the planner can use the index. The two forms are equivalent for non-NULL elements.

Sorting and deduplicating

PostgreSQL has no built-in function to sort an array in older releases (newer servers may ship one; run \df array_* in psql to see what your version provides). The portable approach is to unnest, aggregate, and order inside the aggregate:

SELECT array_agg(DISTINCT t ORDER BY t) AS sorted_unique_tags
FROM products, unnest(tags) AS t;

                 sorted_unique_tags
------------------------------------------------------
 {display,hardware,input,sale,typing,usb,usb-c,wireless}

If you do this often, wrap it in a small SQL function:

CREATE OR REPLACE FUNCTION array_sort_unique(anyarray)
RETURNS anyarray
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT array_agg(x ORDER BY x) FROM (SELECT DISTINCT unnest($1) AS x) s;
$$;
 
SELECT array_sort_unique(ARRAY[3, 1, 2, 3, 1]);   -- {1,2,3}

The anyarray pseudo-type makes the function polymorphic, so it works for text[], int[] and numeric[] alike.

Aggregating over array elements

Because unnest produces a normal row set, every ordinary aggregate applies. To summarize the price history per product:

SELECT p.name,
       min(x)                 AS lowest,
       max(x)                 AS highest,
       round(avg(x), 2)       AS average,
       count(x)               AS points
FROM products p
LEFT JOIN LATERAL unnest(p.prices) AS x ON true
GROUP BY p.name
ORDER BY p.name;

   name   | lowest | highest | average | points
----------+--------+---------+---------+--------
 Keyboard |  34.99 |   45.00 |   41.24 |      4
 License  |        |         |         |      0
 Monitor  | 279.00 |  299.00 |  289.00 |      2
 Mouse    |  19.99 |   19.99 |   19.99 |      1

For a per-row calculation without a GROUP BY, use a scalar subquery: (SELECT max(x) FROM unnest(prices) x) AS highest.

Common errors and how to fix them

cannot subscript type

ERROR:  cannot subscript type text because it does not support subscripting

You applied [n] to a column that is not an array, often because a driver returned the array as a string or the column was declared text instead of text[]. Fix the column type, or cast: (col::text[])[1].

malformed array literal

ERROR:  malformed array literal: "a,b,c"
DETAIL:  Array value must start with "{" or dimension information.

The literal form requires braces. Either write '{a,b,c}', use ARRAY['a','b','c'], or split the string with string_to_array('a,b,c', ',').

Multidimensional dimension mismatch

ERROR:  multidimensional arrays must have array expressions with matching dimensions

Every sub-array in a multidimensional array must have the same length; ARRAY[[1,2],[3]] is rejected. If you need ragged rows, store an array of arrays as JSONB or normalize into a child table.

array_length returns NULL

Not an error, but a frequent bug in WHERE array_length(tags, 1) > 0 clauses: rows with empty arrays are dropped from both the true and false branches. Use cardinality(tags) > 0 or tags <> '{}'.

Quick reference table

FAQ

How do I get the length of a PostgreSQL array?

Use cardinality(arr) for a total element count that returns 0 for empty arrays, or array_length(arr, 1) when you specifically need the length of one dimension and can handle a NULL result for empty arrays.

How do I convert a Postgres array to a comma-separated string?

Call array_to_string(arr, ','). Pass a third argument such as array_to_string(arr, ',', 'NULL') if you want NULL elements to appear in the output instead of being skipped.

Are PostgreSQL arrays zero-indexed?

No. Subscripts start at 1 by default, and reading an out-of-range subscript returns NULL rather than raising an error.

Should I use an array column or a separate table?

Use an array when the values are small, always fetched together with the parent row, and rarely need their own foreign keys or per-element metadata. Use a child table when you need referential integrity, per-element attributes, or heavy per-element querying. A GIN index closes much of the query-performance gap for simple containment searches.

Can I see array values in a GUI client?

Yes. Clients such as Chat2DB (opens in a new tab) display text[] and numeric[] columns in the result grid using the same brace literal syntax PostgreSQL uses, and you can edit them inline or run any of the queries above in the SQL editor.