Array difference in postgresql

Question

I have two arrays [1,2,3,4,7,6] and [2,3,7] in PostgreSQL which may have common elements. What I am trying to do is to exclude from the first array all the elements that are present in the second. So far I have achieved the following:

SELECT array
  (SELECT unnest(array[1, 2, 3, 4, 7, 6])
   EXCEPT SELECT unnest(array[2, 3, 7]));

However, the ordering is not correct as the result is {4,6,1} instead of the desired {1,4,6}. How can I fix this ?

I finally created a custom function with the following definition (taken from here) which resolved my issue:

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 could install the [intarray](https://www.postgresql.org/docs/current/intarray.html) extension which offers an operator for that. — , Mar 22 '19 at 17:52

score 9 · Accepted Answer · answered Mar 22 '19 at 17:09

9

I would use ORDINALITY option of UNNEST and put an ORDER BY in the array_agg function while converting it back to array. NOT EXISTS is preferred over except to make it simpler.

SELECT array_agg(e order by id) 
   FROM unnest( array[1, 2, 3, 4, 7, 6] ) with ordinality as s1(e,id)
    WHERE not exists 
   (
     SELECT 1 FROM unnest(array[2, 3, 7]) as s2(e)
      where s2.e = s1.e
    )

DEMO

answered Mar 22 '19 at 17:09

Kaushik Nayak

30,772
5
32
45

I will accept the answer since it resolves the initial question. However, I ended up creating a custom function which computes the "difference" of two arrays, the definition of which I have added in the initial question. – Mewtwo Mar 26 '19 at 09:10

score -2 · Answer 2 · answered Dec 02 '22 at 08:37

Postgres is unfortunately lacking this functionality. In my case, what I really needed to do was to detect cases where the array difference was not empty. In that specific case you can do that with the @> operator which means "Does the first array contain the second?"

ARRAY[1,4,3] @> ARRAY[3,1,3] → t

See doc

Array difference in postgresql

2 Answers2

Linked