Postgres 12

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 mytable
    ("id" int, "date" date, "sku" int, "store" int, "Units" int, "Vx" varchar(1));

INSERT INTO mytable
    ("id", "date", "sku", "store", "Units", "Vx")
VALUES
    (0, '2019-10-01', 103993.0, 001, 0.0, 'F'),
    (1, '2019-10-02', 103993.0, 001, 1.0, 'F'),
    (2, '2019-10-03', 103993.0, 001, 0, 'F'),
    (3, '2019-10-04', 103993.0, 001, 1.0, 'F'),
    (4, '2019-10-05', 103993.0, 001, 0, 'F'),
    (5, '2019-10-02', 103994.0, 002, 1.0, 'F'),
    (5, '2019-10-03', 103994.0, 002, 0, 'F'),
    (6, '2019-10-04', 103994.0, 002, 1.0, 'F'),
    (7, '2019-10-05', 103994.0, 002, 0.0, 'F');

CREATE TABLE



INSERT 0 9


SELECT
    min("date") as min_date,
    store,
    "Vx",
    json_agg("Units") as units
FROM mytable
GROUP BY store, "Vx"


min_date
store
Vx
units


2019-10-01
1
F
[0, 1, 0, 1, 0]

2019-10-02
2
F
[1, 0, 1, 0]



SELECT 2


SELECT
    jsonb_build_object(
        'date', min("date"),
        'join_store_Vx', json_build_array(store, "Vx"),
        'Units', json_agg("Units")
    )
FROM mytable
GROUP BY store, "Vx"


jsonb_build_object


{"date": "2019-10-01", "Units": [0, 1, 0, 1, 0], "join_store_Vx": [1, "F"]}

{"date": "2019-10-02", "Units": [1, 0, 1, 0], "join_store_Vx": [2, "F"]}



SELECT 2