PostgreSQL - Search for missing combinations

Question

Suppose we have two columns and we know possible values:

a = [1,2]
b = [1,2,3]

So, the table with all combinations will be:

a, b
----
1, 1
1, 2
1, 3
2, 1
2, 2
2, 3

But what if the table is not full?:

a, b
----
1, 1
--
--
--
2, 2
2, 3

How to find the missing combinations?:

a, b
----
1, 2
1, 3
2, 1

P.S. The table is quite large actually, it contains 1-2 million records.
It contains other values, not only that we have to check, those values should be ignored.
Actually, it is not necessary to get all the missing combinations.
Find at least one is enough.

I guess the idea is: select distinct a-values, select distinct b-values, cross join them. SELECT * FROM CROSS_JOIN EXCEPT SELECT EXISTING_COMBINATIONS — Sergey
– Sergey, Commented Sep 7, 2022 at 10:51

user330315 · Accepted Answer · 2022-09-07 10:59:43Z

1

You can use EXCEPT to get that result:

select t1.a, t2.b
from unnest(array[1,2]) as t1(a)
  cross join unnest(array[1,2,3]) as t2(b)
except  
select a, b
from the_table
order by 1,2

The first part generates all possible values, and the EXCEPT will then return those that are in the first query, but not in the second (which is the content of your table)

answered Sep 7, 2022 at 10:59

user330315

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

Collectives™ on Stack Overflow

PostgreSQL - Search for missing combinations

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related