add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table sval(id,closing_price,opening_price,ISIN,closing_fx,market_cap)
as values(104, 55.3, 44, 'KKJJ102',0,0)
,(432, 99 , 77, 'JJII333',0,0)
,(444, 44 , 33, 'KKJJ102',0,0)
,(888, 33 , 41, 'JJEOD23',0,0)
,(422, 99 , 77, 'JJII333',0,0)
,(222, 33 , 41, 'JJEOD23',0,0)
,(777, 77 , 77, 'XXXXXXX',0,0)
,(999, 99 , 99, 'XXXXXXX',0,0);
SELECT 8
--horizontal, paired
select a.id, b.id
from sval as a
join sval as b
on a.isin=b.isin
and a.id<b.id
and (to_jsonb(a)-'id')<>(to_jsonb(b)-'id');
id id
104 444
777 999 
SELECT 2
--vertical, has to be paired based on isin
select a.id, a.isin
from sval as a
join sval as b using(isin)
where(to_jsonb(a)-'id')<>(to_jsonb(b)-'id');
id isin
104 KKJJ102
444 KKJJ102
777 XXXXXXX
999 XXXXXXX 
SELECT 4
--vertical, with diff
select a.id, a.isin, diff
from sval as a
join sval as b using(isin)
cross join lateral
(select jsonb_object_agg(k,v1) as diff
from jsonb_each(to_jsonb(a))a(k,v1)
join jsonb_each(to_jsonb(b))b(k,v2)using(k)
where v1<>v2
and k<>'id') as d
where(to_jsonb(a)-'id')<>(to_jsonb(b)-'id');
id isin diff
104 KKJJ102 {"closing_price": 55.3, "opening_price": 44}
444 KKJJ102 {"closing_price": 44, "opening_price": 33}
777 XXXXXXX {"closing_price": 77, "opening_price": 77}
999 XXXXXXX {"closing_price": 99, "opening_price": 99} 
SELECT 4
--horizontal, paired, with diff
select a.id, b.id, ldiff, rdiff
from sval as a
join sval as b
on a.isin=b.isin
and a.id<b.id
and (to_jsonb(a)-'id')<>(to_jsonb(b)-'id')
cross join lateral
(select jsonb_object_agg(k,v1) as ldiff
, jsonb_object_agg(k,v2) as rdiff
from jsonb_each(to_jsonb(a))a(k,v1)
join jsonb_each(to_jsonb(b))b(k,v2)using(k)
where v1<>v2
and k<>'id') as d;
id id ldiff rdiff
104 444 {"closing_price": 55.3, "opening_price": 44} {"closing_price": 44, "opening_price": 33}
777 999 {"closing_price": 77, "opening_price": 77} {"closing_price": 99, "opening_price": 99} 
SELECT 2