Postgres 15

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

create table table1(id int, rate text, range text,joins text, result int);
insert into table1 values
(1,'x','y','a,b',0),
(2,'x','y','a,c,f,i',0),
(3,'x','y','a,d,o,x,y',0),
(4,'x','y','a,"m/f"',0),
(5,'x','y','a,f',0),
(6,'x','y','a,g,x',0),
(7,'x','y','a,h',0),
(8,'x','y','a,i,l,r,s',0),
(9,'x','y','a,j',0),
(10,'x','y','a,k',0)
returning *;

create table table2(x1 int,	x2 int,	x3 int,	x4 int,	x5 int,	x6 int,	x7 int,	x8 int,	x9 text, x10 int, x11 text,	x12 int,a  int, b  int, c  int, d  int, "m/f"  text, f  text, g  int, h  int, i  int, j  int, k  int, l  int, m  int, n  int, o  int, p  int, q  int, r  int, s  int, t  int, u  int, v  int, w  int, x  int, y  int, z  int, aa  int, bb  int, cc  int);
insert into table2 values
(1,0,0,0,0,0,0,0,0,0,0,0,18,2,2,22,0,2,1,2,1,3,1,2,1,3,26,2,0,22,0,22,2,32,2,4,2,2,1,3,0),
(2,0,0,0,0,0,0,0,0,0,0,0,20,2,2,2,'b/g',0,0,2,1,4,0,2,1,4,24,0,0,2,0,2,1,3,2,5,0,0,0,4,0),
(3,0,0,0,0,0,0,0,0,0,0,0,10,2,2,222,0,2,1,2,1,2,1,2,1,2,24,0,2,2,0,2,1,3,1,5,0,2,1,2,0),
(4,0,0,0,0,0,0,0,0,0,0,0,12,2,2,3,0,0,0,0,1,3,0,0,1,3,21,2,0,0,0,0,0,22,1,4,2,0,0,3,0),
(5,0,0,0,0,0,0,0,0,0,0,0,15,2,2,3,0,0,0,0,1,3,0,0,1,3,21,2,0,2,0,2,1,22,1,4,2,0,0,3,0),
(6,0,0,0,0,0,0,0,0,0,0,0,20,2,2,2,'f/f',0,0,0,1,4,0,0,1,4,20,2,0,2,0,0,0,22,2,4,2,0,0,4,0),
(7,0,0,0,0,0,0,0,0,0,0,0,15,2,2,22,0,0,0,0,1,2,0,0,1,2,21,2,0,2,0,0,0,22,2,4,2,0,0,2,9),
(8,0,0,0,0,0,0,0,0,0,0,0,18,2,2,22,0,0,0,0,1,3,0,0,1,3,21,2,0,2,0,0,0,22,1,4,2,0,0,3,0),
(9,0,0,0,0,0,0,0,0,0,0,0,8,2,0,22,'f/f',2,1,0,1,3,1,0,1,3,24,0,0,2,0,0,0,3,2,5,0,2,1,3,0),
(10,0,0,0,0,0,0,0,0,0,0,0,14,2,2,3,0,2,1,0,1,3,1,0,1,3,12,0,2,22,0,2,1,22,2,3,0,2,1,3,0),
(11,0,0,0,0,0,0,0,0,0,0,0,14,2,0,222,0,22,2,2,2,2,2,2,2,2,20,0,0,0,0,0,0,3,3,4,0,22,2,2,0)

  returning *;


CREATE TABLE




id
rate
range
joins
result


1
x
y
a,b
0

2
x
y
a,c,f,i
0

3
x
y
a,d,o,x,y
0

4
x
y
a,"m/f"
0

5
x
y
a,f
0

6
x
y
a,g,x
0

7
x
y
a,h
0

8
x
y
a,i,l,r,s
0

9
x
y
a,j
0

10
x
y
a,k
0



INSERT 0 10



CREATE TABLE




x1
x2
x3
x4
x5
x6
x7
x8
x9
x10
x11
x12
a
b
c
d
m/f
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z
aa
bb
cc


1
0
0
0
0
0
0
0
0
0
0
0
18
2
2
22
0
2
1
2
1
3
1
2
1
3
26
2
0
22
0
22
2
32
2
4
2
2
1
3
0

2
0
0
0
0
0
0
0
0
0
0
0
20
2
2
2
b/g
0
0
2
1
4
0
2
1
4
24
0
0
2
0
2
1
3
2
5
0
0
0
4
0

3
0
0
0
0
0
0
0
0
0
0
0
10
2
2
222
0
2
1
2
1
2
1
2
1
2
24
0
2
2
0
2
1
3
1
5
0
2
1
2
0

4
0
0
0
0
0
0
0
0
0
0
0
12
2
2
3
0
0
0
0
1
3
0
0
1
3
21
2
0
0
0
0
0
22
1
4
2
0
0
3
0

5
0
0
0
0
0
0
0
0
0
0
0
15
2
2
3
0
0
0
0
1
3
0
0
1
3
21
2
0
2
0
2
1
22
1
4
2
0
0
3
0

6
0
0
0
0
0
0
0
0
0
0
0
20
2
2
2
f/f
0
0
0
1
4
0
0
1
4
20
2
0
2
0
0
0
22
2
4
2
0
0
4
0

7
0
0
0
0
0
0
0
0
0
0
0
15
2
2
22
0
0
0
0
1
2
0
0
1
2
21
2
0
2
0
0
0
22
2
4
2
0
0
2
9

8
0
0
0
0
0
0
0
0
0
0
0
18
2
2
22
0
0
0
0
1
3
0
0
1
3
21
2
0
2
0
0
0
22
1
4
2
0
0
3
0

9
0
0
0
0
0
0
0
0
0
0
0
8
2
0
22
f/f
2
1
0
1
3
1
0
1
3
24
0
0
2
0
0
0
3
2
5
0
2
1
3
0

10
0
0
0
0
0
0
0
0
0
0
0
14
2
2
3
0
2
1
0
1
3
1
0
1
3
12
0
2
22
0
2
1
22
2
3
0
2
1
3
0

11
0
0
0
0
0
0
0
0
0
0
0
14
2
0
222
0
22
2
2
2
2
2
2
2
2
20
0
0
0
0
0
0
3
3
4
0
22
2
2
0



INSERT 0 11


SELECT a,"m/f",
count (*) FROM table2 GROUP BY
a,"m/f"
HAVING count(*) >=1 order by count desc


a
m/f
count


14
0
2

15
0
2

18
0
2

10
0
1

20
b/g
1

12
0
1

20
f/f
1

8
f/f
1



SELECT 8


SELECT count (*) 
  FROM table2 
  GROUP BY a,"m/f"
HAVING count(*) >=1 order by count desc;


count


2

2

2

1

1

1

1

1



SELECT 8


do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin
  for rec in select joins from table1 loop
     select rec.joins into current_joins;
     execute format('select count(*) 
                     from (
                        select 1
                        from table2 
                        group by %1$s 
                        having count(*)>=1 
                     ) as some_alias;',
                     current_joins) 
              into current_result;
     update table1 set result=current_result where joins=current_joins;
  end loop;
end $$;

DO


table table1;


id
rate
range
joins
result


1
x
y
a,b
7

2
x
y
a,c,f,i
9

3
x
y
a,d,o,x,y
11

4
x
y
a,"m/f"
8

5
x
y
a,f
9

6
x
y
a,g,x
10

7
x
y
a,h
10

8
x
y
a,i,l,r,s
10

9
x
y
a,j
9

10
x
y
a,k
9



SELECT 10

id	rate	range	joins
1	x	y	a,b
2	x	y	a,c,f,i
3	x	y	a,d,o,x,y
4	x	y	a,"m/f"
5	x	y	a,f
6	x	y	a,g,x
7	x	y	a,h
8	x	y	a,i,l,r,s
9	x	y	a,j
10	x	y	a,k

x1	a	b	c	d	m/f	f	g	h	i	j	k	l	m	n	o	p	q	r	t	u	v	w	x	y	z	aa	bb	cc
1	18	2	2	22	0	2	1	2	1	3	1	2	1	3	26	2	0	22	22	2	32	2	4	2	2	1	3	0
2	20	2	2	2	b/g	0	0	2	1	4	0	2	1	4	24	0	0	2	2	1	3	2	5	0	0	0	4	0
3	10	2	2	222	0	2	1	2	1	2	1	2	1	2	24	0	2	2	2	1	3	1	5	0	2	1	2	0
4	12	2	2	3	0	0	0	0	1	3	0	0	1	3	21	2	0	0	0	0	22	1	4	2	0	0	3	0
5	15	2	2	3	0	0	0	0	1	3	0	0	1	3	21	2	0	2	2	1	22	1	4	2	0	0	3	0
6	20	2	2	2	f/f	0	0	0	1	4	0	0	1	4	20	2	0	2	0	0	22	2	4	2	0	0	4	0
7	15	2	2	22	0	0	0	0	1	2	0	0	1	2	21	2	0	2	0	0	22	2	4	2	0	0	2	9
8	18	2	2	22	0	0	0	0	1	3	0	0	1	3	21	2	0	2	0	0	22	1	4	2	0	0	3	0
9	8	2	0	22	f/f	2	1	0	1	3	1	0	1	3	24	0	0	2	0	0	3	2	5	0	2	1	3	0
10	14	2	2	3	0	2	1	0	1	3	1	0	1	3	12	0	2	22	2	1	22	2	3	0	2	1	3	0
11	14	2	0	222	0	22	2	2	2	2	2	2	2	2	20	0	0	0	0	0	3	3	4	0	22	2	2	0

x1	a	b	c	d	m/f	f	g	h	i	j	k	l	m	n	o	p	q	r	t	u	v	w	x	y	z	aa	bb	cc
1	18	2	2	22	0	2	1	2	1	3	1	2	1	3	26	2	0	22	22	2	32	2	4	2	2	1	3	0
2	20	2	2	2	b/g	0	0	2	1	4	0	2	1	4	24	0	0	2	2	1	3	2	5	0	0	0	4	0
3	10	2	2	222	0	2	1	2	1	2	1	2	1	2	24	0	2	2	2	1	3	1	5	0	2	1	2	0
4	12	2	2	3	0	0	0	0	1	3	0	0	1	3	21	2	0	0	0	0	22	1	4	2	0	0	3	0
5	15	2	2	3	0	0	0	0	1	3	0	0	1	3	21	2	0	2	2	1	22	1	4	2	0	0	3	0
6	20	2	2	2	f/f	0	0	0	1	4	0	0	1	4	20	2	0	2	0	0	22	2	4	2	0	0	4	0
7	15	2	2	22	0	0	0	0	1	2	0	0	1	2	21	2	0	2	0	0	22	2	4	2	0	0	2	9
8	18	2	2	22	0	0	0	0	1	3	0	0	1	3	21	2	0	2	0	0	22	1	4	2	0	0	3	0
9	8	2	0	22	f/f	2	1	0	1	3	1	0	1	3	24	0	0	2	0	0	3	2	5	0	2	1	3	0
10	14	2	2	3	0	2	1	0	1	3	1	0	1	3	12	0	2	22	2	1	22	2	3	0	2	1	3	0
11	14	2	0	222	0	22	2	2	2	2	2	2	2	2	20	0	0	0	0	0	3	3	4	0	22	2	2	0

x1	a	b	c	d	m/f	f	g	h	i	j	k	l	m	n	o	p	q	r	t	u	v	w	x	y	z	aa	bb	cc
1	18	2	2	22	0	2	1	2	1	3	1	2	1	3	26	2	0	22	22	2	32	2	4	2	2	1	3	0
2	20	2	2	2	b/g	0	0	2	1	4	0	2	1	4	24	0	0	2	2	1	3	2	5	0	0	0	4	0
3	10	2	2	222	0	2	1	2	1	2	1	2	1	2	24	0	2	2	2	1	3	1	5	0	2	1	2	0
4	12	2	2	3	0	0	0	0	1	3	0	0	1	3	21	2	0	0	0	0	22	1	4	2	0	0	3	0
5	15	2	2	3	0	0	0	0	1	3	0	0	1	3	21	2	0	2	2	1	22	1	4	2	0	0	3	0
6	20	2	2	2	f/f	0	0	0	1	4	0	0	1	4	20	2	0	2	0	0	22	2	4	2	0	0	4	0
7	15	2	2	22	0	0	0	0	1	2	0	0	1	2	21	2	0	2	0	0	22	2	4	2	0	0	2	9
8	18	2	2	22	0	0	0	0	1	3	0	0	1	3	21	2	0	2	0	0	22	1	4	2	0	0	3	0
9	8	2	0	22	f/f	2	1	0	1	3	1	0	1	3	24	0	0	2	0	0	3	2	5	0	2	1	3	0
10	14	2	2	3	0	2	1	0	1	3	1	0	1	3	12	0	2	22	2	1	22	2	3	0	2	1	3	0
11	14	2	0	222	0	22	2	2	2	2	2	2	2	2	20	0	0	0	0	0	3	3	4	0	22	2	2	0