I have a Table in Postgres 12 which tracks what Items i are use in which Versions v:

CREATE TABLE compare_test(v BIGINT, i BIGINT);

With example data:

INSERT INTO compare_test VALUES
 (1,21),
 (1,22),
 (1,23),
 (2,21),
 (2,22),
 (2,23),
 (3,21),
 (3,22);

I'm trying to create a View that returns:

Queries used to compare missing values in two Tables like:

SELECT l.v as source_v, l.i as source_i,
           r.v as target_v, r.i as target_i
  FROM compare_test l
    LEFT JOIN
        compare_test r ON r.i = l.i
  WHERE r.i IS NULL;

and

SELECT l.v as source_v, l.i as source_i
  FROM compare_test l
  WHERE NOT EXISTS
      (
        SELECT i as target_i
          FROM compare_test r
          WHERE r.i = l.i
      )

do not seem to work if the joined Table is the same Table or if more than 2 Versions are in the Table.

I don't have the option to change the Database Structure but I can use plugins.

CodePudding user response：

The solution below gives those results.
It makes re-use of a CTE.

(but somehow I got a feeling that there should exist a more efficient way)

with cte1 as (
  SELECT i
  , count(*) cnt
  , min(v) min_v
  , max(v) max_v
  FROM compare_test
  GROUP BY i
)
, cte2 as
(
  select *
  from cte1 as c1
  where not exists (
    select 1
    from cte1 c2
    where c2.min_v = c1.min_v
      and c2.max_v < c1.max_v
   ) 
) 
select distinct
  t.v as source_v
, c1.max_v as target_v
, c2.i as source_i
, null as target_i
from cte2 c2
left join compare_test t
  on t.i = c2.i
left join cte1 c1
  on t.v between c1.min_v and c1.max_v
  and c1.i != t.i
order by source_v

But if it's not really required to follow the relations, then it becomes really simple.
Then it's just a left join of the existing to all possible combinations.

select distinct 
  src.v as source_v
, missing.v as target_v
, src.i as source_i
, missing.i as target_i
from
(
  select ver.v, itm.i
  from (select distinct v from compare_test) as ver
  cross join (select distinct i from compare_test) as itm
  left join compare_test t
    on t.v = ver.v and t.i = itm.i
  where t.v is null
) as missing
left join compare_test as src
  on src.i = missing.i and src.v != missing.v
order by target_i, target_v, source_v

source_v | target_v | source_i | target_i
-------: | -------: | -------: | -------:
       1 |        5 |       21 |       21
       2 |        5 |       21 |       21
       3 |        5 |       21 |       21
       1 |        5 |       22 |       22
       2 |        5 |       22 |       22
       3 |        5 |       22 |       22
       1 |        3 |       23 |       23
       2 |        3 |       23 |       23
       1 |        5 |       23 |       23
       2 |        5 |       23 |       23
       5 |        1 |       44 |       44
       5 |        2 |       44 |       44
       5 |        3 |       44 |       44

db<>fiddle here