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

From: Sergey Prokhorenko <sergeyprokhorenko(at)yahoo(dot)com(dot)au>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
Date: 2025-10-23 12:34:43
Message-ID: 181705213.487328.1761222883245@mail.yahoo.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2025-10-23 12:40:14 Re: Making pg_rewind faster
Previous Message Robert Haas 2025-10-23 12:21:22 Re: pgsql: Use CompactAttribute more often, when possible