PostgreSQL finally learns to read Roman numerals

Open source development is rarely the hobbyist pastime it is sometimes made out to be. Most of the work that goes into projects like PostgreSQL is driven by money, directly or indirectly: customers sponsor features, and companies that build products and services around the database invest in its long-term health. But there is more to it than commercial interest. Contributions are visible and earn respect, developers want the tools they use to be better, and emotional attachment to a project is a powerful motivator. And sometimes, there is just room for fun.

That fun is not always obvious from the documentation or from everyday use of PostgreSQL, both of which speak in a technical, no-nonsense tone. It emerges in places like the pgsql-hackers mailing list and, more often, at conferences. It also shows up in the occasional patch that solves a problem nobody knew they had.

A quarter-century gap in functionality

PostgreSQL has been able to format numbers as Roman numerals using to_char() since commit b866d2e2d7 in 2000. That feature probably originated from a desire for compatibility with Oracle database, which supports the same conversion. No discussion of the patch survives on the mailing list.

What was missing, until recently, was the reverse direction: parsing Roman numerals back into numbers. That support landed nearly 25 years later in commit 172e6b3adb. Notably, the mailing list discussion for that patch does not include anyone questioning why it was needed. The patch fills a gap, and supporting Roman numerals is simply cool. Try submitting a similar enhancement request to Oracle and imagine how quickly it would be dismissed.

Trying out the new conversion in v18

The new functionality is available in the upcoming PostgreSQL v18. A quick test shows how it behaves:

1

2

3

4

5

SELECT to_number('MCMLXVIII', 'RN');

to_number

═══════════

      1968

The basic conversion from a Roman numeral string to an integer works as expected.

1

2

SELECT to_number('MMMM', 'RN');

ERROR:  invalid Roman numeral

As with the formatting function to_char(), the maximum supported number for parsing is 3999.

1

2

3

4

5

SELECT to_number('iv', 'RN');

to_number

═══════════

         4

to_number() also accepts lowercase Roman numerals without complaint.

1

2

SELECT to_number('IIII', 'RN');

ERROR:  invalid Roman numeral

What it does not accept is the non-standard alternative spelling of 4 as IIII. The function sticks to the canonical representation.

A small feature with a wider point

Few people will have felt the absence of Roman numeral parsing in PostgreSQL. But the feature exists anyway, which is exactly the point. It demonstrates that open source projects can entertain ideas that are not strictly business requirements and still deliver something useful, or at least pleasant. The database world is a slightly richer place for it.