Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table htab (
  id int generated always as identity primary key,
  hcol hstore not null
);

insert into htab (hcol) values
('surface => concrete, ramp => yes, incline => up, highway => footway'),
('surface => asphalt, lit => no, source => survey, incline => 12.6%, highway => footway'),
('bicycle => yes, surface => asphalt, highway => footway, segregated => no');

CREATE TABLE



INSERT 0 3


with expand as (
  select id, k, v
    from htab 
         cross join lateral each(hcol) as c(k, v)
), pairings as (
  select t1.id, t1.k as key1, t2.k as key2
    from expand t1
         join expand t2
            on t2.id = t1.id
           and t2.k < t1.k
)
select key1, key2, count(*)
  from pairings
 group by key1, key2
 order by key1 desc, key2 desc;
  


key1
key2
count


surface
source
1

surface
segregated
1

surface
ramp
1

surface
lit
1

surface
incline
2

surface
highway
3

surface
bicycle
1

source
lit
1

source
incline
1

source
highway
1

segregated
highway
1

segregated
bicycle
1

ramp
incline
1

ramp
highway
1

lit
incline
1

lit
highway
1

incline
highway
2

highway
bicycle
1



SELECT 18

key1	key2	count
surface	source	1
surface	segregated	1
surface	ramp	1
surface	lit	1
surface	incline	2
surface	highway	3
surface	bicycle	1
source	lit	1
source	incline	1
source	highway	1
segregated	highway	1
segregated	bicycle	1
ramp	incline	1
ramp	highway	1
lit	incline	1
lit	highway	1
incline	highway	2
highway	bicycle	1