Postgres 11

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

-- cast not works because MS takes only 3 decimal digits

SELECT TO_TIMESTAMP('2019-05-30T05:48:29.095705642+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')
ERROR:  date/time field value out of range: "2019-05-30T05:48:29.095705642+01:00"

-- cast with MS works after reducing the decimal digits 

SELECT TO_TIMESTAMP('2019-05-30T05:48:29.095+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')

to_timestamp

2019-05-30 05:48:29.095+01

-- alternatively change MS to US for higher precision (6 decimal digits)

SELECT TO_TIMESTAMP('2019-05-30T05:48:29.095705+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"')

to_timestamp

2019-05-30 05:48:29.095705+01

-- using the 9 pattern (any digit, can be dropped) to mask any un-convertable digit:
-- .US999"Z"
SELECT TO_TIMESTAMP('2019-05-30T05:48:29.095705642+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.US999"Z"')

to_timestamp

2019-05-30 05:48:29.095705+01

-- simple postgres magic
SELECT '2019-05-30T05:48:29.095705642+01:00'::timestamptz

timestamptz

2019-05-30 05:48:29.095706+01

-- after cast, convert the time zone

SELECT '2019-05-30T05:48:29.095705642+01:00'::timestamptz at time zone 'UTC'

timezone

2019-05-30 04:48:29.095706