How to Remove Elements from Array in PostgreSQL

In PostgreSQL, you can remove elements from an array using the array_remove(array, element) function, which returns a new array without the specified element. This function removes all occurrences of the element from the array.

Syntax

The basic syntax to remove an element from an array is:

• array_remove(array, element): Returns a new array with all occurrences of element removed from array.

sql

array_remove(anyarray, anyelement) → 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}

Common Pitfalls

One common mistake is trying to remove an element by index instead of by value, but array_remove only removes by value. Also, it removes all occurrences of the element, not just the first one.

If you want to remove by index, you need a different approach using array slicing or functions.

sql

/* Wrong: Trying to remove by index (does not work) */
SELECT array_remove(ARRAY[10, 20, 30], 2) AS wrong_result;

/* Right: Remove by value */
SELECT array_remove(ARRAY[10, 20, 30], 20) AS correct_result;

Output

wrong_result
{10,20,30}

correct_result
{10,30}

Quick Reference

Summary of array removal in PostgreSQL:

Function	Description	Example
array_remove(array, element)	Removes all occurrences of element from array	array_remove(ARRAY[1,2,3,2], 2) → {1,3}
array_replace(array, old, new)	Replaces all occurrences of old with new	array_replace(ARRAY[1,2,3], 2, NULL) → {1,NULL,3} (does not remove element)

Key Takeaways

Use array_remove(array, element) to remove all occurrences of an element from an array.

array_remove removes by value, not by index.

To remove by index, use array slicing or other array functions.

array_remove returns a new array and does not modify the original array in place.