PostgreSQL - Search for missing combinations-CodePudding

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.

CodePudding user response：

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)