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 |