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?
6 Answers
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)
6 Comments
Christophe Roussy
This requirement seems like something trivial, something very common, why is there no native function for this, is anything planned for this ?
klin
Well, there is no native function even in python. On the other hand, there is nothing to prevent you from creating the function in your database and treating it as native.
Christophe Roussy
How does this compare to the following variant:
select array(select unnest(:arr1) except select unnest(:arr2));. I suppose your variant it is a bit better on large arrays because it does less unnesting ?klin
@ChristopheRoussy - yes, the variant in the answer should be faster (maybe not so much).
Dex
What does
all() do in this case? |
With some help from this post:
select array_agg(elements) from
(select unnest('{1,2,3,4}'::int[])
except
select unnest('{1,4}'::int[])) t (elements)
Result:
{2,3}
1 Comment
ffxsam
Unfortunately this solution (and others on this page) are close, but are not sufficient. In some cases, they can alter the array order. E.g., in this answer, try replacing
{1,4} above with {2,4}. The result is {3,1}.With the intarray extension, you can simply use -:
select '{1,2,3,4}'::int[] - '{1,4}'::int[]
Result:
{2,3}
You'll need to install the intarray extension if you didn't already. It adds many convenient functions and operators if you're dealing with arrays of integers.
3 Comments
You need the
intarray extension for that to work: postgresql.org/docs/current/static/intarray.html
Ondřej Želazko
"just use
-" and a need for installing an extension is contradictory, isn't it?
Denys Séguret
@OndřejŽelazko You're right. Reworded that. But well, when dealing with such arrays, this should be standard in your pg installation...
This answer is the simplest I think: https://stackoverflow.com/a/6535089/673187
SELECT array(SELECT unnest(:array1) EXCEPT SELECT unnest(:array2));
so you can easily use it in an UPDATE command, when you need to remove some elements from an array column:
UPDATE table1 SET array1_column=(SELECT array(SELECT unnest(array1_column) EXCEPT SELECT unnest('{2, 3}'::int[])));
Comments
You can use this function for when you are dealing with bigint/int8 numbers and want to maintain order:
CREATE OR REPLACE FUNCTION arr_subtract(int8[], int8[])
RETURNS int8[] AS
$func$
SELECT ARRAY(
SELECT a
FROM unnest($1) WITH ORDINALITY x(a, ord)
WHERE a <> ALL ($2)
ORDER BY ord
);
$func$ LANGUAGE sql IMMUTABLE;
I got this solution from the following answer to a similar question: https://stackoverflow.com/a/8584080/1544473
Comments
User array re-dimension annotation
array[<start index>:<end index>]
WITH t(stack, dim) as (
VALUES(ARRAY[1,2,3,4], ARRAY[1,4])
) SELECT stack[dim[1]+1:dim[2]-1] FROM t
array_remove()but unless you install theintarraymodule you can't easily remove one array from anotherarray_remove; for more you can looping witharray_remove.