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 inventory (
    id SERIAL PRIMARY KEY,
    inventory_date DATE,
    product_name VARCHAR(255),
    product_value VARCHAR(255)
);

INSERT INTO inventory
(inventory_date, product_name, product_value)
VALUES 
('2020-10-19', 'Product_A', '400'),
('2020-10-22', 'Product_B', '400'),
('2020-11-20', 'Product_C', '900'),
('2020-11-25', 'Product_D', '300');


4 rows affected

SELECT iv.product_name,
       extract(month from age('2020-12-20'::date, MAX(iv.inventory_date::date))) AS months_in_inventory
FROM inventory iv
GROUP BY 1
ORDER BY 1;


product_name
months_in_inventory


Product_A
2

Product_B
1

Product_C
1

Product_D
0


SELECT iv.product_name,
       (extract(year from age('2020-12-20'::date, MAX(iv.inventory_date::date))) * 12 +
        extract(month from age('2020-12-20'::date, MAX(iv.inventory_date::date)))
       ) AS months_in_inventory
FROM inventory iv
GROUP BY 1
ORDER BY 1;


product_name
months_in_inventory


Product_A
2

Product_B
1

Product_C
1

Product_D
0