PostgreSQL 16 improves infinity: PgSQLPhriday #012

This month's #pgsqlphriday challenge is the 12th PgSQL Phriday, marking the end of the first year of the event! Before getting into this month's topic I want to give a shout out to Ryan Booz for starting #pgsqlphriday. More importantly though, a huge thank you to the hosts and contributors from the past year! I looked forward to seeing the topic each month followed by waiting to see who all would contribute and how they would approach the topic.

Check out pgsqlphriday.com for the full list of topics, including recaps from each topic to link to contributing posts. This month is the 7th topic I've been able to contribute to the event. I even had the honor of hosting #005 with the topic Is your data relational? I'm really looking forward to another year ahead!

Now returning to your regularly scheduled PgSQL Phriday content.

This month, Ryan Booz chose the topic: What Excites You About PostgreSQL 16? With the release of Postgres 16 expected in the near(ish) future, it's starting to get real. It won't be long until casual users are upgrading their Postgres instances. To decide what to write about I headed to the Postgres 16 release notes to scan through the documents. Through all of the items, I picked this item attributed to Vik Fearing.

• Accept the spelling "+infinity" in datetime input

The rest of this post looks at what this means, and why I think this matters.

What +infinity means

The use of +infinity means an infinitely positive value. For datetimes, as is the scope of the change here, that means "forever in the future." The +infinity value is in contrast to -infinity, or "forever in the past."

I suspect some readers are thinking "but Ryan, infinity has existed for a long time." While being funny in a way I appreciate, these readers would also be correct! The value infinity has been supported by Postgres for a long time. The nuance here is the preceding +, specific to the datetime data types such as TIMESTAMPTZ.

In Postgres 15 and prior, this query casting +infinity to TIMESTAMPTZ fails.

(pg15 🐘) postgres@postgres=# SELECT '+infinity'::TIMESTAMPTZ;

The error received from running the above query:

ERROR:  invalid input syntax for type timestamp with time zone: "+infinity"

Starting with Postgres 16, the same command with +infinity returns infinity as expected.

(pg16b3 🐘) postgres@postgres=# SELECT '+infinity'::TIMESTAMPTZ;
┌─────────────┐
│ timestamptz │
╞═════════════╡
│ infinity    │
└─────────────┘

Why I care

The main reason I like this improvement supporting +infinity with datetime data types is that this query already works in Postgres 15.

SELECT '+infinity'::NUMERIC;

As an end user of Postgres, if I have a query like above works but then the following query fails, that's an annoying inconsistency.

SELECT '+infinity'::TIMESTAMPTZ;

While minor syntax inconsistencies are easy to write off as trivial, having consistent syntax and operation is critical for the trust in key backend systems like Postgres. No one wants to spend time trying to figure out why '+infinity'::NUMERIC works but '+infinity'::TIMESTAMPTZ does not.

Postgres Beta in Docker

This is a bonus section tangential to the topic, because testing beta versions is often seen as intimidating. No wonder, if you read my post from 2019 about installing Postgres and PostGIS from source, it's an intimidating topic. There's a lot of words in there!

By contrast, you can quickly spool up Postgres 16 beta 3 using the official Postgres Docker image. For this post I ran the following docker run command, making Postgres 16 beta 3 available on port 5416.

docker run --name some-postgres -p 5416:5432 \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -d postgres:16beta3

I can then connect to the Postgres 16 Beta 3 container using this command.

psql -h localhost -p 5416 -d postgres -U postgres

Postgres 16 RC 1 was released 8/31/2023.

Summary

The title of this post sums it up perfectly: PostgreSQL 16 improves infinity!

This concludes the regularly scheduled PgSQL Phriday content. I can't wait to see everyone else's contributions!

Until next time...

🐘