Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://stackoverflow.com/q/78031456/5298879
create table test(ID int,	Brand int,	Type int,
                  c4 int,
                  c5 int,
                  c6 int,
                  c7 int,
                  c8 int,
                  c9 int);

select setseed(.42);

insert into test
select     random()*1e4,
       1e4+random()*1e4,
       2e4+random()*1e4,
       3e4+random()*1e4,
       4e4+random()*1e4,
       5e4+random()*1e4,
       6e4+random()*1e4,
       7e4+random()*1e4,
       8e4+random()*1e4
from generate_series(1,2e5);

select * from test tablesample bernoulli(.42)repeatable(.42) limit 15;
/*values
 (1,'Honda','Coupe')
,(1,'Jeep','SUV')
,(2,'Ford','Sedan')
,(2,'Ford','Crossover')
returning *;

Example for above table:
Rows with ID = 1 have different Brand and Type values, so I want one result row for each column.
For ID = 2 there is only one brand, but multiple types, so only one result row for type.

The desired result would be like this.

ID	Difference
1	Brand
1	Type
2	Type
*/

CREATE TABLE




setseed






SELECT 1



INSERT 0 200000




id
brand
type
c4
c5
c6
c7
c8
c9


5406
10478
29046
38449
44496
55074
68172
74584
87934

3465
14907
29123
39548
46451
57513
62910
71397
84466

5842
10604
20761
31703
41733
53773
66585
72173
89260

4173
19195
27689
37219
45848
59277
68091
77971
84355

1210
11935
26408
39596
49776
50866
60655
74617
82212

4870
17905
27343
36696
43259
54288
68535
79564
89594

2679
16349
22851
33296
44071
52235
67321
75772
80868

9566
12445
23890
35271
46700
53388
68073
71130
87643

2668
15921
24970
30929
46606
58573
69491
70638
87227

8267
15148
28228
37841
47532
53426
69230
76746
85078

739
15224
27414
38020
42262
50113
61903
72333
84289

9163
13258
25858
38201
41625
58371
66654
70750
84082

9442
12728
24179
39847
44531
53720
64969
75504
81096

837
12667
22673
33029
40106
51451
60638
78311
81721

8425
14378
23607
36038
49425
53496
65083
72707
89154



SELECT 15


explain analyze verbose
SELECT t.id, c.difference
FROM  (
   SELECT id
        , min(brand) <> max(brand) AS b_diff
        , min(type)  <> max(type)  AS t_diff
        , min(c4)  <> max(type)  AS c4_diff
        , min(c5)  <> max(type)  AS c5_diff
        , min(c6)  <> max(type)  AS c6_diff
        , min(c7)  <> max(type)  AS c7_diff
        , min(c8)  <> max(type)  AS c8_diff
        , min(c9)  <> max(type)  AS c9_diff
   FROM   test
   GROUP  BY id
   ) t
JOIN   LATERAL (
   VALUES
     ('Brand', t.b_diff)
   , ('Type' , t.t_diff)
   , ('c4' , t.c4_diff)
   , ('c5' , t.c5_diff)
   , ('c6' , t.c6_diff)
   , ('c7' , t.c7_diff)
   , ('c8' , t.c8_diff)
   , ('c9' , t.c9_diff)
   ) c(difference, diff) ON c.diff
--ORDER  BY 1, 2;  -- optional


QUERY PLAN


Nested Loop  (cost=7346.71..7411.21 rows=800 width=36) (actual time=270.622..396.041 rows=80008 loops=1)

  Output: test.id, "*VALUES*".column1

  ->  Finalize GroupAggregate  (cost=7346.71..7381.21 rows=200 width=12) (actual time=270.604..362.947 rows=10001 loops=1)

        Output: test.id, (min(test.brand) <> max(test.brand)), (min(test.type) <> max(test.type)), (min(test.c4) <> max(test.type)), (min(test.c5) <> max(test.type)), (min(test.c6) <> max(test.type)), (min(test.c7) <> max(test.type)), (min(test.c8) <> max(test.type)), (min(test.c9) <> max(test.type))

        Group Key: test.id

        ->  Gather Merge  (cost=7346.71..7369.71 rows=200 width=44) (actual time=270.593..353.891 rows=19996 loops=1)

              Output: test.id, (PARTIAL min(test.brand)), (PARTIAL max(test.brand)), (PARTIAL min(test.type)), (PARTIAL max(test.type)), (PARTIAL min(test.c4)), (PARTIAL min(test.c5)), (PARTIAL min(test.c6)), (PARTIAL min(test.c7)), (PARTIAL min(test.c8)), (PARTIAL min(test.c9))

              Workers Planned: 1

              Workers Launched: 1

              ->  Sort  (cost=6346.70..6347.20 rows=200 width=44) (actual time=217.147..218.174 rows=9998 loops=2)

                    Output: test.id, (PARTIAL min(test.brand)), (PARTIAL max(test.brand)), (PARTIAL min(test.type)), (PARTIAL max(test.type)), (PARTIAL min(test.c4)), (PARTIAL min(test.c5)), (PARTIAL min(test.c6)), (PARTIAL min(test.c7)), (PARTIAL min(test.c8)), (PARTIAL min(test.c9))

                    Sort Key: test.id

                    Sort Method: quicksort  Memory: 1244kB

                    Worker 0:  actual time=187.323..188.291 rows=9995 loops=1

                      Sort Method: quicksort  Memory: 1243kB

                    ->  Partial HashAggregate  (cost=6337.06..6339.06 rows=200 width=44) (actual time=196.037..198.918 rows=9998 loops=2)

                          Output: test.id, PARTIAL min(test.brand), PARTIAL max(test.brand), PARTIAL min(test.type), PARTIAL max(test.type), PARTIAL min(test.c4), PARTIAL min(test.c5), PARTIAL min(test.c6), PARTIAL min(test.c7), PARTIAL min(test.c8), PARTIAL min(test.c9)

                          Group Key: test.id

                          Batches: 1  Memory Usage: 3745kB

                          Worker 0:  actual time=151.957..154.508 rows=9995 loops=1

                            Batches: 1  Memory Usage: 3745kB

                          ->  Parallel Seq Scan on public.test  (cost=0.00..2912.35 rows=124535 width=36) (actual time=0.014..49.782 rows=100000 loops=2)

                                Output: test.id, test.brand, test.type, test.c4, test.c5, test.c6, test.c7, test.c8, test.c9

                                Worker 0:  actual time=0.018..41.760 rows=72080 loops=1

  ->  Values Scan on "*VALUES*"  (cost=0.00..0.10 rows=4 width=32) (actual time=0.000..0.002 rows=8 loops=10001)

        Output: "*VALUES*".column1, "*VALUES*".column2

        Filter: "*VALUES*".column2

Planning Time: 0.458 ms

Execution Time: 398.847 ms



EXPLAIN


explain analyze verbose
select id, k
from test, json_each_text(to_json(test)) as a(k,v)
group by id, k
having min(v)<>max(v);


QUERY PLAN


Finalize GroupAggregate  (cost=379145.84..381895.84 rows=19900 width=36) (actual time=6719.348..7127.746 rows=80008 loops=1)

  Output: test.id, a.k

  Group Key: test.id, a.k

  Filter: (min(a.v) <> max(a.v))

  Rows Removed by Filter: 10001

  ->  Gather Merge  (cost=379145.84..381445.84 rows=20000 width=100) (actual time=6719.333..7051.593 rows=179973 loops=1)

        Output: test.id, a.k, (PARTIAL min(a.v)), (PARTIAL max(a.v))

        Workers Planned: 1

        Workers Launched: 1

        ->  Sort  (cost=378145.83..378195.83 rows=20000 width=100) (actual time=6684.736..6697.014 rows=89986 loops=2)

              Output: test.id, a.k, (PARTIAL min(a.v)), (PARTIAL max(a.v))

              Sort Key: test.id, a.k

              Sort Method: external merge  Disk: 2592kB

              Worker 0:  actual time=6650.340..6661.945 rows=89991 loops=1

                Sort Method: external merge  Disk: 2592kB

              ->  Partial HashAggregate  (cost=376517.06..376717.06 rows=20000 width=100) (actual time=5312.347..6413.813 rows=89986 loops=2)

                    Output: test.id, a.k, PARTIAL min(a.v), PARTIAL max(a.v)

                    Group Key: test.id, a.k

                    Batches: 5  Memory Usage: 6705kB  Disk Usage: 19400kB

                    Worker 0:  actual time=5268.918..6365.757 rows=89991 loops=1

                      Batches: 5  Memory Usage: 6705kB  Disk Usage: 19328kB

                    ->  Nested Loop  (cost=0.01..251982.35 rows=12453471 width=68) (actual time=0.063..3550.680 rows=900000 loops=2)

                          Output: test.id, a.k, a.v

                          Worker 0:  actual time=0.059..3476.992 rows=891720 loops=1

                          ->  Parallel Seq Scan on public.test  (cost=0.00..2912.35 rows=124535 width=64) (actual time=0.025..150.734 rows=100000 loops=2)

                                Output: test.id, test.*

                                Worker 0:  actual time=0.031..183.810 rows=99080 loops=1

                          ->  Function Scan on pg_catalog.json_each_text a  (cost=0.01..1.00 rows=100 width=64) (actual time=0.026..0.029 rows=9 loops=200000)

                                Output: a.k, a.v

                                Function Call: json_each_text(to_json(test.*))

                                Worker 0:  actual time=0.024..0.028 rows=9 loops=99080

Planning Time: 0.410 ms

Execution Time: 7132.906 ms



EXPLAIN
id	brand	type	c4	c5	c6	c7	c8	c9
5406	10478	29046	38449	44496	55074	68172	74584	87934
3465	14907	29123	39548	46451	57513	62910	71397	84466
5842	10604	20761	31703	41733	53773	66585	72173	89260
4173	19195	27689	37219	45848	59277	68091	77971	84355
1210	11935	26408	39596	49776	50866	60655	74617	82212
4870	17905	27343	36696	43259	54288	68535	79564	89594
2679	16349	22851	33296	44071	52235	67321	75772	80868
9566	12445	23890	35271	46700	53388	68073	71130	87643
2668	15921	24970	30929	46606	58573	69491	70638	87227
8267	15148	28228	37841	47532	53426	69230	76746	85078
739	15224	27414	38020	42262	50113	61903	72333	84289
9163	13258	25858	38201	41625	58371	66654	70750	84082
9442	12728	24179	39847	44531	53720	64969	75504	81096
837	12667	22673	33029	40106	51451	60638	78311	81721
8425	14378	23607	36038	49425	53496	65083	72707	89154