22

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?

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85
k.xf
  • 359
  • 1
  • 3
  • 10
  • 5
    You can remove individual elements using `array_remove()` but unless you install the [`intarray`](https://www.postgresql.org/docs/current/static/intarray.html) module you can't easily remove one array from another –  Jun 20 '16 at 08:52
  • for small amount of values you can use multiple time `array_remove`; for more you can looping with `array_remove`. – Sławomir Lenart Oct 23 '17 at 10:25
  • Does this answer your question? [How to compare two arrays and pick only the non matching elements In postgres](https://stackoverflow.com/questions/6533029/how-to-compare-two-arrays-and-pick-only-the-non-matching-elements-in-postgres) – Majid Sep 02 '20 at 14:06

6 Answers6

21

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)
klin
  • 112,967
  • 15
  • 204
  • 232
  • This requirement seems like something trivial, something very common, why is there no native function for this, is anything planned for this ? – Christophe Roussy Mar 01 '19 at 18:57
  • Well, there is no *native* function [even in python.](https://stackoverflow.com/q/4211209/1995738) On the other hand, there is nothing to prevent you from creating the function in your database and treating it as native. – klin Mar 01 '19 at 19:43
  • 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 ? – Christophe Roussy Feb 04 '20 at 18:31
  • @ChristopheRoussy - yes, the variant in the answer should be faster (maybe not so much). – klin Feb 04 '20 at 18:59
  • What does `all()` do in this case? – Dex May 30 '22 at 13:29
  • @Dex - [Row and Array Comparisons](https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.17) – klin May 30 '22 at 13:51
17

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}
MichaelG
  • 652
  • 1
  • 10
  • 17
  • 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}`. – ffxsam Oct 02 '20 at 23:54
12

With the intarray extension, you can simply use -:

select '{1,2,3,4}'::int[] - '{1,4}'::int[]

Result:

{2,3}

Online demonstration

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.

Denys Séguret
  • 372,613
  • 87
  • 782
  • 758
  • 3
    You need the `intarray` extension for that to work: https://www.postgresql.org/docs/current/static/intarray.html –  Jun 20 '16 at 08:49
  • 1
    "just use `-`" and a need for installing an extension is contradictory, isn't it? – Ondřej Želazko Aug 22 '17 at 12:45
  • 2
    @OndřejŽelazko You're right. Reworded that. But well, when dealing with such arrays, this should be standard in your pg installation... – Denys Séguret Aug 22 '17 at 12:47
7

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[])));
Majid
  • 3,128
  • 1
  • 26
  • 31
2

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

Adé
  • 475
  • 5
  • 15
0

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
Dror Dromi
  • 157
  • 1
  • 4