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

Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>

From: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>

To: pgsql-hackers <pgsql-hackers@postgresql.org>

Date: 2025-10-23T12:34:43Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add base32hex support to encode() and decode() functions.
- 497c1170cb10 19 (unreleased) landed
Allow explicit casting between bytea and uuid.
- ba21f5bf8aff 19 (unreleased) landed
Add support for base64url encoding and decoding
- e1d917182c19 19 (unreleased) cited

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