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 table_a (
    id int,
    mycolumn timestamptz DEFAULT now()
);
INSERT INTO table_a(id) VALUES (1);

SELECT * FROM table_a
1 rows affected



id
mycolumn


1
2021-01-25 14:08:00.349595+00


ALTER TABLE table_a ALTER COLUMN mycolumn TYPE timestamp USING mycolumn AT TIME ZONE 'CET';

SELECT * FROM table_a;


id
mycolumn


1
2021-01-25 15:08:00.349595


CREATE TABLE table_b (
    id int,
    mycolumn tstzrange[] DEFAULT ARRAY[tstzrange(now(), now() + interval '1 day')]
);
INSERT INTO table_b(id) VALUES (1);

SELECT * FROM table_b
1 rows affected



id
mycolumn


1
{"["2021-01-25 14:08:00.389168+00","2021-01-26 14:08:00.389168+00")"}


ALTER TABLE table_b RENAME COLUMN mycolumn TO oldcolumn;
ALTER TABLE table_b ADD COLUMN mycolumn tsrange[];

SELECT * FROM table_b;


id
oldcolumn
mycolumn


1
{"["2021-01-25 14:08:00.389168+00","2021-01-26 14:08:00.389168+00")"}
null


SELECT 
    id,
    array_agg(tsrange(
        lower(tsdata) AT TIME ZONE 'CET',
        upper(tsdata) AT TIME ZONE 'CET'
    ))
FROM
    table_b,
    unnest(oldcolumn) tsdata
GROUP BY id


id
array_agg


1
{"["2021-01-25 15:08:00.389168","2021-01-26 15:08:00.389168")"}


UPDATE table_b b
SET mycolumn = s.mycolumn
FROM (
    SELECT 
        id,
        array_agg(tsrange(
            lower(tsdata) AT TIME ZONE 'CET',
            upper(tsdata) AT TIME ZONE 'CET'
        )) as mycolumn
    FROM
        table_b,
        unnest(oldcolumn) tsdata
    GROUP BY id
) s
WHERE s.id = b.id;

ALTER TABLE table_b DROP COLUMN oldcolumn;

SELECT * FROM table_b
1 rows affected



id
mycolumn


1
{"["2021-01-25 15:08:00.389168","2021-01-26 15:08:00.389168")"}