Postgres array comparison - find missing elements

Question

I have the table below.

╔════════════════════╦════════════════════╦═════════════╗
║id                  ║arr1                ║arr2         ║  
╠════════════════════╬════════════════════╬═════════════╣             
║1                   ║{1,2,3,4}           ║{2,1,7}      ║
║2                   ║{0}                 ║{3,4,5}      ║
╚════════════════════╩════════════════════╩═════════════╝

I want to find out the elements which are in arr1 and not in arr2.
Expected output

╔════════════════════╦════════════════════╗
║id                  ║diff                ║  
╠════════════════════╬════════════════════╣             
║1                   ║{3,4}               ║      
║2                   ║{0}                 ║
╚════════════════════╩════════════════════╝

If I have 2 individual arrays, I can do as follows:

select array_agg(elements)
from (
  select unnest(array[0])
  except
  select unnest(array[3,4,5])
) t (elements)

But I am unable to integrate this code to work by selecting from my table. Any help would be highly appreciated. Thank you!!

Answer 1

I would write a function for this:

create function array_diff(p_one int[], p_other int[])
  returns int[]
as
$$
  select array_agg(item)
  from (
     select *
     from unnest(p_one) item
     except
     select *
     from unnest(p_other)
  ) t 
$$
language sql
stable;

Then you can use it like this:

select id, array_diff(arr1, arr2)
from the_table

---

A much faster alternative is to install the intarray module and use

select id, arr1 - arr2
from the_table