Postgres 16

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 tbl (id int, brand text, TYPE text);
INSERT INTO tbl VALUES
  (1, 'Honda', 'Coupe')
, (1, 'Jeep', 'SUV')
, (2, 'Ford', 'Sedan')
, (2, 'Ford', 'Crossover')
RETURNING *;

CREATE TABLE




id
brand
type


1
Honda
Coupe

1
Jeep
SUV

2
Ford
Sedan

2
Ford
Crossover



INSERT 0 4


-- min & max
SELECT t.id, c.difference
FROM  (
   SELECT id
        , min(brand) <> max(brand) AS b_diff
        , min(type)  <> max(type)  AS t_diff
   FROM   tbl
   GROUP  BY id
   ) t
JOIN   LATERAL (
   VALUES
     ('Brand', t.b_diff)
   , ('Type' , t.t_diff)
   ) c(difference, diff) ON c.diff
ORDER  BY 1, 2;  -- optional


id
difference


1
Brand

1
Type

2
Type



SELECT 3


-- count distinct
SELECT t.id, c.difference
FROM  (
   SELECT id
        , count(DISTINCT brand) AS brand_ct
        , count(DISTINCT type)  AS type_ct
   FROM   tbl
   GROUP  BY id
   ) t
JOIN   LATERAL (
   VALUES
     ('Brand', t.brand_ct)
   , ('Type' , t.type_ct)
   ) c(difference, count) ON c.count > 1
ORDER  BY 1, 2;  -- optional


id
difference


1
Brand

1
Type

2
Type



SELECT 3