Postgres 13

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 elbat
             (cat_id integer,
              cat_name text,
              piece integer,
              is_tag_stock boolean);

CREATE TABLE


INSERT INTO elbat
            (cat_id,
             cat_name,
             piece,
             is_tag_stock)
            VALUES (4,
                    'cat1',
                    20,
                    true),
                   (5,
                    'cat2',
                    30,
                    false),
                   (4,
                    'cat1',
                    40,
                    false),
                   (4,
                    'cat1',
                    50,
                    false);

INSERT 0 4


SELECT cat_id,
       cat_name,
       sum(CASE
             WHEN is_tag_stock THEN
               piece
             ELSE
               0
           END) tagged,
       sum(CASE
             WHEN NOT is_tag_stock THEN
               piece
             ELSE
               0
           END) untagged
       FROM elbat
       GROUP BY cat_id,
                cat_name;


cat_id
cat_name
tagged
untagged


4
cat1
20
90

5
cat2
0
30



SELECT 2


SELECT cat_id,
       cat_name,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN is_tag_stock THEN
                                  piece
                              ELSE
                                0
                              END)) tagged,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN NOT is_tag_stock THEN
                                  piece
                                ELSE
                                  0
                             END)) untagged
       FROM elbat
       GROUP BY cat_id,
                cat_name;


cat_id
cat_name
tagged
untagged


4
cat1
{"piece": 20}
{"piece": 90}

5
cat2
{"piece": 0}
{"piece": 30}



SELECT 2

cat_id	cat_name	tagged	untagged
4	cat1	{"piece": 20}	{"piece": 90}
5	cat2	{"piece": 0}	{"piece": 30}