Postgres 16

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

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