Exclude matched array elements

Question

How can I exclude matched elements of one array from another?

Postgres code:

a1 := '{1, 2, 5, 15}'::int[];
a2 := '{1, 2, 3, 6, 7, 9, 15}'::int[];

a3 := a2 ??magic_operator?? a1;

In a3 I expect exactly '{3, 6, 7, 9}'

Final Result

My and lad2025 solutions works fine.

Solution with array_position() required PostgreSQL 9.5 and later, executes x3 faster.

Answer 1

It looks like XOR between arrays:

WITH set1 AS
(
 SELECT * FROM unnest('{1, 2, 5, 15}'::int[])
), set2 AS
(
 SELECT * FROM unnest('{1, 2, 3, 6, 7, 9, 15}'::int[])
), xor AS
(
  (SELECT * FROM set1
   UNION 
   SELECT * FROM set2)
  EXCEPT
  (SELECT * FROM set1
   INTERSECT 
   SELECT * FROM set2)
)
SELECT array_agg(unnest ORDER BY unnest)
FROM xor

Output:

"{3,5,6,7,9}"

How it works:

1. Unnest both arrays
2. Calculate SUM
3. Calculate INTERSECT
4. From SUM - INTERSECT
5. Combine to array

Alternatively you could use sum of both minus(except) operations:

(A+B) - (A^B)
<=>
(A-B) + (B-A)

---

Utilizing FULL JOIN:

WITH set1 AS
(
 SELECT *
FROM unnest('{1, 2, 5, 15}'::int[])
), set2 AS
(
 SELECT *
 FROM unnest('{1, 2, 3, 6, 7, 9, 15}'::int[])
)
SELECT array_agg(COALESCE(s1.unnest, s2.unnest) 
                 ORDER BY COALESCE(s1.unnest, s2.unnest))
FROM set1 s1
FULL JOIN set2 s2
  ON s1.unnest = s2.unnest
WHERE s1.unnest IS NULL
  OR s2.unnest IS NULL;

EDIT:

If you want only elements from second array that are not is first use simple EXCEPT:

SELECT array_agg(unnest ORDER BY unnest)
FROM (SELECT * FROM unnest('{1, 2, 3, 6, 7, 9, 15}'::int[])
      EXCEPT
      SELECT * FROM unnest('{1, 2, 5, 15}'::int[])) AS sub

Output:

"{3,6,7,9}"

Answer 2

The additional module intarray provides a simple and fast subtraction operator - for integer arrays, exactly the magic_operator you are looking for:

test=# SELECT '{1, 2, 3, 6, 7, 9, 15}'::int[] - '{1, 2, 5, 15}'::int[] AS result;
 ?column?
-----------
 {3,6,7,9}

You need to install the module once per database:

CREATE EXTENSION intarray;

It also provides special operator classes for indexes:

• Postgresql intarray error: undefined symbol: pfree

Note that it only works for:

... null-free arrays of integers.

Answer 3

I found a little similar case and modify.

That SQL solve my case.

with elements (element) as (
   select unnest(ARRAY[1, 2, 3, 6, 7, 9, 15])
)
select array_agg(element)
from elements
where array_position(ARRAY[1, 2, 5, 15],element) is null

PostgreSQL 9.5 and later required.