Postgres 15

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 tab(id INT, value INT);

INSERT INTO tab(id, value)
VALUES (1, 100), (2, NULL), (3, NULL),
       (4, NULL), (5, NULL), (6, NULL),
       (7, NULL), (8, 200), (9, NULL);

CREATE TABLE



INSERT 0 9


SELECT *
FROM tab
ORDER BY id;


id
value


1
100

2
null

3
null

4
null

5
null

6
null

7
null

8
200

9
null



SELECT 9


WITH cte AS (
  SELECT *,
    ARRAY_REMOVE((ARRAY_AGG(value) OVER(/*PARTITION BY any_group*/ ORDER BY id)), NULL) 
       AS value_arr
  FROM tab
)
SELECT id, value,
    value_arr[array_upper(value_arr, 1)] AS new_value,
    value_arr -- debug
FROM cte
ORDER BY id;


id
value
new_value
value_arr


1
100
100
{100}

2
null
100
{100}

3
null
100
{100}

4
null
100
{100}

5
null
100
{100}

6
null
100
{100}

7
null
100
{100}

8
200
200
{100,200}

9
null
200
{100,200}



SELECT 9


SELECT *,
    (ARRAY_REMOVE((ARRAY_AGG(value) OVER( ORDER BY id)), NULL))
    [ARRAY_UPPER(ARRAY_REMOVE((ARRAY_AGG(value) OVER( ORDER BY id)), NULL), 1) ]
       AS new_value
FROM tab
ORDER BY id


id
value
new_value


1
100
100

2
null
100

3
null
100

4
null
100

5
null
100

6
null
100

7
null
100

8
200
200

9
null
200



SELECT 9


SELECT *,
 to_json(ARRAY_REMOVE((ARRAY_AGG(value) OVER(/*PARTITION BY any_group*/ ORDER BY id))
         ,NULL))->-1 AS value_arr
FROM tab
ORDER BY id;


id
value
value_arr


1
100
100

2
null
100

3
null
100

4
null
100

5
null
100

6
null
100

7
null
100

8
200
200

9
null
200



SELECT 9

id	value
1	100
2	null
3	null
4	null
5	null
6	null
7	null
8	200
9	null

id	value	new_value	value_arr
1	100	100	{100}
2	null	100	{100}
3	null	100	{100}
4	null	100	{100}
5	null	100	{100}
6	null	100	{100}
7	null	100	{100}
8	200	200	{100,200}
9	null	200	{100,200}