PostgreSQL 18 reference · Array Functions

PostgreSQL ARRAY_REMOVE() Function

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

PostgreSQL 18 Since 9.3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

array_remove(array, element)  |  array_replace(array, from, to)

Parameter	Description
`array`	An array value.
`element`	The value to remove.

A new array.

Remove a value

SELECT array_remove(ARRAY[1, 2, 1, 3], 1) AS removed;

Output

 removed
---------
 {2,3}
(1 row)

Run this on your computer — see “Get Started” for setup.

Replace values and drop NULLs

SELECT array_replace(ARRAY['a', 'x', 'c'], 'x', 'b') AS replaced, array_remove(ARRAY[1, NULL, 2], NULL) AS no_nulls;

Output

 replaced | no_nulls
----------+----------
 {a,b,c}  | {1,2}
(1 row)

Run this on your computer — see “Get Started” for setup.

Version	Change
PostgreSQL 9.3	Added

ARRAY_APPEND() · Add an element to the end of an array. array_prepend() adds to the start, and || joins arrays or elements.
ARRAY_POSITION() · Return the 1-based position of the first occurrence of a value in an array, or NULL if it isn't there.
SQL String Functions · tutorial