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 data(
 month int,
 year int,
 sales int,
 year_mean float
);

INSERT INTO data VALUES
(1,2021,100,NULL),
(2,2021,150,NULL),
(3,2021,500,NULL),
(1,2022,100,NULL),
(2,2022,140,NULL),
(3,2022,120,NULL),
(1,2023,100,NULL),
(2,2023,100,NULL),
(3,2023,100,NULL);


CREATE TABLE



INSERT 0 9


-- Calulate mean per year
SELECT year, avg(sales) FROM data
GROUP BY year;


year
avg


2022
120.0000000000000000

2023
100.0000000000000000

2021
250.0000000000000000



SELECT 3


UPDATE data
SET year_mean = subquery.mean
FROM (
  SELECT year, avg(sales) as "mean" FROM data
  GROUP BY year
) as subquery
WHERE data.year = (subquery.year - 1);

UPDATE 6


SELECT * FROM data;


month
year
sales
year_mean


1
2023
100
null

2
2023
100
null

3
2023
100
null

1
2021
100
120

2
2021
150
120

3
2021
500
120

1
2022
100
100

2
2022
140
100

3
2022
120
100



SELECT 9

year	avg
2022	120.0000000000000000
2023	100.0000000000000000
2021	250.0000000000000000

month	year	sales	year_mean
1	2023	100	null
2	2023	100	null
3	2023	100	null
1	2021	100	120
2	2021	150	120
3	2021	500	120
1	2022	100	100
2	2022	140	100
3	2022	120	100