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?.

show timezone;

TimeZone

GB

SELECT (timestamptz '2020-07-28 01:10:00+0')::time AT TIME ZONE 'Europe/London' AS timetz;

timetz

02:10:00+01

SELECT (timestamptz '2020-07-28 01:10:00+0')::time AT TIME ZONE 'Europe/London' AS timetz
UNION ALL
SELECT '03:00:00'::time;

timetz

02:10:00+01

03:00:00+01

-- seems ok?
SELECT (timestamptz '2020-07-28 01:10:00+0')::time AT TIME ZONE 'Europe/London' > '03:00:00'::time;

?column?

f

-- but it's a loaded footgun, really!
SET timezone = 'EUROPE/Vienna';
SELECT (timestamptz '2020-07-28 01:10:00+0')::time AT TIME ZONE 'Europe/London' AS timetz
UNION ALL
SELECT '03:00:00'::time;

timetz

02:10:00+01

03:00:00+02

-- boom !!!
SELECT (timestamptz '2020-07-28 01:10:00+0')::time AT TIME ZONE 'Europe/London' > '03:00:00'::time;

?column?

t