Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions

Hi pgsql-hackers,

I'm writing to propose adding two new built-in functions to PostgreSQL that provide compact UUID encoding using the base32hex format.

I'm one of the contributors to RFC 9562 (UUIDs) and to the uuidv7() implementations in PostgreSQL and several libraries. I'm writing to express my strong support for a new patch by Andrey Borodin, the developer of the built-in uuidv7() function for PostgreSQL 18. This patch adds two new functions for UUID compact text representation. These functions would be long-awaited additions to PostgreSQL's UUID functionality.

I would like to request the community to review this patch and to consider it for commit.

The patch is available here: https://github.com/x4m/postgres_g/commit/aa902bbc5dfc47d4b35f05016304a1e671abb505 

_______________________________

uuid_to_base32hex ( uuid ) -> text

Encodes a UUID into a 26-character base32hex string (uppercase, no hyphens, without padding), using the alphabet 0123456789ABCDEFGHIJKLMNOPQRSTUV as specified in RFC 4648 (https://datatracker.ietf.org/doc/html/rfc4648#page-10).

To accommodate base32hex encoding (5 bits per character), the 128-bit UUID requires 130 bits total (26 characters * 5 bits). The additional 2 zero bits are appended as padding.

This compact, lexicographically sortable format preserves temporal ordering for UUIDv7, making it ideal for primary keys stored as values in JSON key-value pairs, as well as for URLs, filenames, and other space-constrained contexts.

Example:

uuid_to_base32hex('019535d9-3df7-79fb-b466-fa907fa17f9e'::uuid) -> 06AJBM9TUTSVND36VA87V8BVJO

_______________________________

base32hex_to_uuid ( text ) -> uuid

Decodes a base32hex string back into its original UUID. The input is case-insensitive. Invalid inputs return NULL. The decoding is lossless and produces a bitwise-identical UUID.

Example:

base32hex_to_uuid('06AJBM9TUTSVND36VA87V8BVJO') -> 019535d9-3df7-79fb-b466-fa907fa17f9e

_______________________________

We considered base36 but rejected it due to poor performance. Crockford's Base32 was also rejected due to its lack of native support in standard libraries, making base32hex the most practical choice.

Converter: https://tomeko.net/online_tools/base32hex.php?lang=en

Best regards,

Sergey Prokhorenko