Is it possible to remove multiple elements from an array? Before removing elements Array1 is :
{1,2,3,4}
Array2 that contains some elements I wish to remove:
{1,4}
And I want to get:
{2,3}
How to operate?
Find the index of the array element you want to remove using indexOf , and then remove that index with splice . The splice() method changes the contents of an array by removing existing elements and/or adding new elements. The second parameter of splice is the number of elements to remove.
Deleting an element permanently from an array can be done using the ARRAY_DELETE function.
Postgres has a very natural syntax for grabbing a slice of an array. You simply add brackets after the array declaring the lower and upper bounds of the slice separated by a colon.
You can use unset() function which removes the element from an array and then use array_values() function which indexes the array numerically.
Use unnest()
with array_agg()
, e.g.:
with cte(array1, array2) as (
values (array[1,2,3,4], array[1,4])
)
select array_agg(elem)
from cte, unnest(array1) elem
where elem <> all(array2);
array_agg
-----------
{2,3}
(1 row)
If you often need this functionality, define the simple function:
create or replace function array_diff(array1 anyarray, array2 anyarray)
returns anyarray language sql immutable as $$
select coalesce(array_agg(elem), '{}')
from unnest(array1) elem
where elem <> all(array2)
$$;
You can use the function for any array, not only int[]
:
select array_diff(array['a','b','c','d'], array['a','d']);
array_diff
------------
{b,c}
(1 row)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With