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 TYPE pg_temp.my_int_txt AS (age int, gender text);

CREATE TYPE


-- original, fixed
WITH t as (
    SELECT name, array_agg(DISTINCT("age", "gender")::pg_temp.my_int_txt) as "ages_and_genders"
    FROM (
        SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
    ) as t
    GROUP BY name
)
SELECT name, ("ages_and_genders"[1]).age, ("ages_and_genders"[1]).gender
FROM t
WHERE array_length("ages_and_genders", 1) = 1;


name
age
gender


alice
30
f



SELECT 1


-- formatted, simplified, clarified:
WITH cte AS (
   SELECT name, array_agg(DISTINCT ROW(age, gender)::pg_temp.my_int_txt) AS ag
   FROM  (
     VALUES
       ('bob'  , 33, 'm')
     , ('bob'  , 33, 'f')
     , ('alice', 30, 'f')
     , ('alice', 30, 'f')  -- to cover identical dupes
   ) AS t (name, age, gender)
   GROUP BY name
   )
SELECT name, (ag[1]).*
FROM   cte
WHERE  cardinality(ag) = 1;


name
age
gender


alice
30
f



SELECT 1


-- much better alternative 1:
SELECT name, min(age) AS age, min(gender) AS gender
FROM  (
   VALUES
     ('bob'  , 33, 'm')
   , ('bob'  , 33, 'f')
   , ('alice', 30, 'f')
   , ('alice', 30, 'f')
   ) AS t (name, age, gender)
GROUP  BY name
HAVING count(DISTINCT (age, gender)) = 1;


name
age
gender


alice
30
f



SELECT 1


-- much better alternative 2:
WITH cte(name, age, gender) AS (
   VALUES
     ('bob'  , 33, "char" 'm')
   , ('bob'  , 33, 'f')
   , ('alice', 30, 'f')
   , ('alice', 30, 'f')
   )
SELECT DISTINCT ON (name) *
FROM   cte t
WHERE  NOT EXISTS (
   SELECT FROM cte t1
   WHERE t1.name = t.name
   AND  (t1.age, t1.gender) IS DISTINCT FROM (t.age, t.gender)
   );


name
age
gender


alice
30
f



SELECT 1