How to handle infinity timestamptz?

Executing:

await pg_acursor.execute("SELECT * FROM pg_roles;")
await pg_acursor.fetchall()

...

  File "/usr/local/lib/python3.10/site-packages/psycopg/cursor_async.py", line 235, in fetchall
    records = self._tx.load_rows(self._pos, self.pgresult.ntuples, self._make_row)
  File "psycopg_binary/_psycopg/transform.pyx", line 463, in psycopg_binary._psycopg.Transformer.load_rows
  File "psycopg_binary/types/datetime.pyx", line 796, in psycopg_binary._psycopg.TimestamptzLoader.cload
psycopg.DataError: timestamp too large (after year 10K): 'infinity'

Mysteriously, this doesn't fail on my local machine (using Docker's postgres:latest). But does fail on a brand new Amazon RDS postgres 16.3 instance. I assume this is the rolvaliduntil timestamptz column and, and Amazon sets the value to infinity instead of null for no good reason.

So... any idea how to coerce postgres to handle such dates gracefully (e.g. parse as null).

Solution

Psycopg allows customisation of how data is read by configuring adapters. To handle infinity in timestamps we can subclass the TimestamptzLoader class. This code is based on the Handling Infinity Date example from the docs.

import psycopg
from psycopg.types.datetime import TimestamptzLoader


class InfTimestamptzLoader(TimestamptzLoader):
    def load(self, data):
        if data == b'infinity':
            return None
        return super().load(data)


# Register the adapter with the global adapter context. This may also be done
# at the connection or cursor level.
psycopg.adapters.register_loader('timestamptz', InfTimestamptzLoader)

with psycopg.connect(dbname='so') as conn, conn.cursor() as cur:
    cur.execute("""SELECT 'infinity'::timestamptz AS ts""")
    print(cur.fetchone())

Output:

(None,)

Alternatively, on the database sid, there is an isfinite function that will return false for infinite timestamps. It could be used inside a CASE statement like this:

import datetime as dt

import psycopg

DROP = """DROP TABLE IF EXISTS t79383492"""
CREATE = """CREATE TABLE t79383492 (ts timestamp with time zone)"""
INSERT = """INSERT INTO t79383492 (ts) VALUES (%s)"""
SELECT = """SELECT CASE WHEN isfinite(ts) THEN ts ELSE NULL END AS ts FROM t79383492"""

with psycopg.connect(dbname='so') as conn, conn.cursor() as cur:
    cur.execute(DROP)
    cur.execute(CREATE)
    cur.executemany(INSERT, [(dt.datetime.now(),), ('infinity',)])
    cur.execute(SELECT)
    for row in cur.fetchall():
        print(row)

Sample output:

(datetime.datetime(2025, 1, 24, 10, 21, 17, 663117, tzinfo=zoneinfo.ZoneInfo(key='Europe/London')),)
(None,)