How to Use array_remove in PostgreSQL: Syntax and Examples

In PostgreSQL, use the array_remove(array, element) function to remove all occurrences of a specified element from an array. It returns a new array without the given element, leaving the original array unchanged.

Syntax

The array_remove function takes two arguments:

• array: The input array from which you want to remove elements.
• element: The value you want to remove from the array.

The function returns a new array with all occurrences of the specified element removed.

sql

array_remove(anyarray, anyelement) RETURNS anyarray

Example

This example shows how to remove the number 3 from an integer array:

sql

SELECT array_remove(ARRAY[1, 2, 3, 4, 3, 5], 3) AS result;

Output

 result 
--------
 {1,2,4,5}
(1 row)

Common Pitfalls

Common mistakes when using array_remove include:

• Expecting it to modify the original array in place. It returns a new array instead.
• Removing elements that do not exist returns the original array unchanged.
• Using it on arrays of different data types than the element causes errors.

sql

/* Wrong: expecting original array to change */
-- This does not update the original array variable

/* Right: assign the result to a new variable or column */
SELECT array_remove(ARRAY['a', 'b', 'c', 'b'], 'b') AS cleaned_array;

Output

 cleaned_array 
---------------
 {a,c}
(1 row)

Quick Reference

Function	Description	Example
array_remove(array, element)	Removes all occurrences of element from array	array_remove(ARRAY[1,2,3,2], 2) → {1,3}
Returns	A new array without the specified element	N/A
Data types	Works with any array and element type matching array	array_remove(ARRAY['a','b','c'], 'b') → {a,c}

Key Takeaways

Use array_remove(array, element) to get a new array without the specified element.

The original array is not changed; assign the result if you want to keep it.

If the element is not found, the original array is returned unchanged.

Ensure the element type matches the array element type to avoid errors.

array_remove works with any array type, including text, integer, and more.