Postgres 10

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 t as
    select *
    from (values ('a', 1), ('a', 2), ('a', 3), ('b', 4), ('c', 5)) v(colxx, colyy)

SELECT 5

select *
from t

colxx
colyy

a
1

a
2

a
3

b
4

c
5

SELECT 5

-- values are the same
select sum(case when colxx in ('b', 'c') then colyy end),
       sum(colyy) filter (where colxx in ('b', 'c'))
from t

sum
sum

9
9

SELECT 1

-- error
select (case when colxx in ('b', 'c') then sum(colyy) end)
from t

ERROR:  column "t.colxx" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: select (case when colxx in ('b', 'c') then sum(colyy) end)
                          ^

-- diferent results
select colxx, (case when colxx in ('b', 'c') then sum(colyy) end)
from t
group by colxx

colxx
case

a
null

b
4

c
5

SELECT 3