PostgreSQL Array Functions – Complete List with Examples

Any type can form an array (integer[], text[]). Write arrays as ARRAY[1, 2, 3] or '{1,2,3}'. Array indexes start at 1.

Name	Description	Since
`ARRAY_LENGTH()`	Return the length of one dimension of an array (use 1 for ordinary arrays). Empty arrays give NULL, not 0.
`CARDINALITY()`	Return the total number of elements in an array across all dimensions, and 0 for an empty array.	9.4
`UNNEST()`	Expand an array into a set of rows, one per element. With several arrays it zips them into columns.
`ARRAY_APPEND()`	Add an element to the end of an array. array_prepend() adds to the start, and \|\| joins arrays or elements.
`ARRAY_CAT()`	Concatenate two arrays of the same element type into one new array. The \|\| operator does the same thing.
`ARRAY_POSITION()`	Return the 1-based position of the first occurrence of a value in an array, or NULL if it isn't there.	9.5
`ARRAY_REMOVE()`	Remove every element equal to a given value from an array. array_replace() swaps them for another value.	9.3
`ARRAY_TO_STRING()`	Join array elements into a string with a separator, optionally writing NULLs as a given text.
`ANY and ALL`	Compare a value with every element of an array: x = ANY(array) is true if any element matches. ALL needs every element to.
`ARRAY_SORT()`	Return a copy of an array with its elements sorted, ascending by default. Added in PostgreSQL 18.	18
`ARRAY_REVERSE()`	Return a copy of an array with its elements in reverse order. Added in PostgreSQL 18.	18

Name

Description

Since

Return the length of one dimension of an array (use 1 for ordinary arrays). Empty arrays give NULL, not 0.

Return the total number of elements in an array across all dimensions, and 0 for an empty array.

9.4

Expand an array into a set of rows, one per element. With several arrays it zips them into columns.

Add an element to the end of an array. array_prepend() adds to the start, and || joins arrays or elements.

Concatenate two arrays of the same element type into one new array. The || operator does the same thing.

Return the 1-based position of the first occurrence of a value in an array, or NULL if it isn't there.

9.5

Remove every element equal to a given value from an array. array_replace() swaps them for another value.

9.3

Join array elements into a string with a separator, optionally writing NULLs as a given text.

Compare a value with every element of an array: x = ANY(array) is true if any element matches. ALL needs every element to.

Return a copy of an array with its elements sorted, ascending by default. Added in PostgreSQL 18.

Return a copy of an array with its elements in reverse order. Added in PostgreSQL 18.