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 orders (
customer_id SERIAL PRIMARY KEY,
order_date date,
order_amount int,
product varchar
);
INSERT INTO orders (order_date, order_amount, product) VALUES
('2022-12-25', 5, 'ботинки'),
('2022-12-25', 3, 'кофты'),
('2022-12-25', 1, 'пальто'),
('2022-12-25', 7, 'ботинки'),
('2022-12-26', 3, 'кофты'),
('2022-12-26', 3, 'ботинки'),
('2022-12-26', 6, 'пальто'),
('2022-12-26', 7, 'пальто'),
('2022-12-26', 5, 'ботинки'),
('2022-12-27', 8, 'ботинки'),
('2022-12-27', 1, 'пальто'),
('2022-12-27', 2, 'кофты'),
('2022-12-27', 4, 'ботинки'),
('2022-12-27', 9, 'ботинки'),
('2022-12-27', 2, 'пальто')
CREATE TABLE
INSERT 0 15
---- Дока https://postgrespro.ru/docs/postgresql/16/tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT *
FROM crosstab(
'SELECT order_date, product, sum(order_amount) AS order_amount
FROM orders
GROUP BY order_date, product
ORDER BY order_date, product'
---, 'VALUES (''кофты''), (''ботинки''), (''пальто'')'
) AS ct (order_date TIMESTAMP, кофты INT, ботинки INT, пальто INT);
CREATE EXTENSION
ERROR: invalid return type DETAIL: SQL rowid datatype does not match return rowid datatype.
---- Дока https://postgrespro.ru/docs/postgresql/16/tablefunc
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT *
FROM crosstab(
'SELECT order_date, product, sum(order_amount) AS order_amount
FROM orders
GROUP BY order_date, product
ORDER BY order_date, product'
, 'VALUES (''кофты''), (''ботинки''), (''пальто'')'
) AS ct (order_date TIMESTAMP, кофты INT, ботинки INT, пальто INT);
CREATE EXTENSION
| order_date | кофты | ботинки | пальто |
|---|---|---|---|
| 2022-12-25 00:00:00 | 3 | 12 | 1 |
| 2022-12-26 00:00:00 | 3 | 8 | 13 |
| 2022-12-27 00:00:00 | 2 | 21 | 3 |
SELECT 3