SQLearnAcademy
←Function reference
Arrays and seriesPostgreSQL only

ARRAY_REMOVE

Returns an array without any elements equal to the given value.

Syntax

ARRAY_REMOVE(<array>, <value>)
ARRAY_REPLACE(<array>, <from>, <to>)

Removes every occurrence of the value, not just the first, and returns a new array. It can remove NULL too: ARRAY_REMOVE(<array>, NULL). To replace a value rather than delete it, use ARRAY_REPLACE.

Remove an element
SELECT ARRAY_REMOVE(ARRAY['sql', 'draft', 'postgres', 'draft'], 'draft') AS cleaned;
cleaned
["sql","postgres"]

ARRAY_REMOVE removes all occurrences, not just the first.

Remove NULL
SELECT ARRAY_REMOVE(ARRAY['sql', NULL, 'postgres'], NULL) AS without_nulls;
without_nulls
["sql","postgres"]
Replace rather than remove
SELECT ARRAY_REPLACE(ARRAY['sql', 'draft', 'postgres'], 'draft', 'final') AS replaced;
replaced
["sql","final","postgres"]

Watch out

It removes all occurrences, not just the first: there's no separate “just one” variant.

Only for one-dimensional arrays: for ARRAY[[1, 2], [3, 4]] you'll get removing elements from multidimensional arrays is not supported.

The function only returns a new array. To change the data in a table, you need UPDATE … SET tags = ARRAY_REMOVE(tags, 'draft').