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!!

Such a function is already provided by the intarray module. I have updated my answer. — user330315
– user330315, Commented Dec 12, 2021 at 10:02

score 2 · Accepted Answer · 2021-12-12 10:01:33Z

2

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

edited Dec 12, 2021 at 10:01

answered Dec 12, 2021 at 7:32

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Stefanov.sm Over a year ago

Why not make it even more generic and reusable like array_diff(p_one anyarray, p_other anyarray) returns anyarray with the same body.

user330315 Over a year ago

@Stefanov.sm: no specific reason.

Pooya · Accepted Answer · 2021-12-12 07:12:26Z

1

You should use except for each id and after that group by for each group

Demo

with diff_data as (
    select id, unnest(arr1) as data
    from test_table
        except
    select id, unnest(arr2) as data
    from test_table
)
select id, array_agg(data order by data) as diff
from diff_data
group by id

answered Dec 12, 2021 at 7:12

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres array comparison - find missing elements

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related