Postgres 16

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

create table test (id int, val1 int,val2 int,val3 int,val4 varchar(10));
insert into test values
 (1,10,20,30,'val1')
,(2,11,21,29,'val2')
,(3, 9, 0,31,'val3')
,(4, 9, 20,null,'val4')
;
select * from test;

CREATE TABLE



INSERT 0 4




id
val1
val2
val3
val4


1
10
20
30
val1

2
11
21
29
val2

3
9
0
31
val3

4
9
20
null
val4



SELECT 4


select *
from test t
where exists( 
  select key::text k,value::text v from json_each(row_to_json(t.*)) eatr
  where value::text='null' 
    or (value::text similar to '[0-9.]{1,}' and cast(coalesce(value::text,'') as int)=0)
  )


id
val1
val2
val3
val4


3
9
0
31
val3

4
9
20
null
val4



SELECT 2


select id
  ,string_agg(case when v='null' 
        or (v similar to '[0-9.]{1,}' and cast(v as int)=0 ) 
       then cast(k as varchar) end
  ,',')
from(
select *
from test t
cross join lateral(select key::text k,value::text v from json_each(row_to_json(t.*)) eatr) atr
)
group by id
order by id


id
string_agg


1
null

2
null

3
val2

4
val3



SELECT 4


select *
from test t
cross join lateral ( 
  select key::text k,trim('"' from value::text) v from json_each(row_to_json(t.*)) eatr
--  where value::text='null' 
--    or (value::text similar to '[0-9.]{1,}' and cast(coalesce(value::text,'') as int)=0)
  ) x


id
val1
val2
val3
val4
k
v


1
10
20
30
val1
id
1

1
10
20
30
val1
val1
10

1
10
20
30
val1
val2
20

1
10
20
30
val1
val3
30

1
10
20
30
val1
val4
val1

2
11
21
29
val2
id
2

2
11
21
29
val2
val1
11

2
11
21
29
val2
val2
21

2
11
21
29
val2
val3
29

2
11
21
29
val2
val4
val2

3
9
0
31
val3
id
3

3
9
0
31
val3
val1
9

3
9
0
31
val3
val2
0

3
9
0
31
val3
val3
31

3
9
0
31
val3
val4
val3

4
9
20
null
val4
id
4

4
9
20
null
val4
val1
9

4
9
20
null
val4
val2
20

4
9
20
null
val4
val3
null

4
9
20
null
val4
val4
val4



SELECT 20

id	val1	val2	val3	val4	k	v
1	10	20	30	val1	id	1
1	10	20	30	val1	val1	10
1	10	20	30	val1	val2	20
1	10	20	30	val1	val3	30
1	10	20	30	val1	val4	val1
2	11	21	29	val2	id	2
2	11	21	29	val2	val1	11
2	11	21	29	val2	val2	21
2	11	21	29	val2	val3	29
2	11	21	29	val2	val4	val2
3	9	0	31	val3	id	3
3	9	0	31	val3	val1	9
3	9	0	31	val3	val2	0
3	9	0	31	val3	val3	31
3	9	0	31	val3	val4	val3
4	9	20	null	val4	id	4
4	9	20	null	val4	val1	9
4	9	20	null	val4	val2	20
4	9	20	null	val4	val3	null
4	9	20	null	val4	val4	val4