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

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Aleksander Alekseev <[email protected]>
To: pgsql-hackers <[email protected]>
Cc: Sergey Prokhorenko <[email protected]>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
Date: Thu, 23 Oct 2025 18:08:29 +0300
Message-ID: <CAJ7c6TNOWMOsUPDHHDs=D-0T_c2XxDuTcrD7XwFNuz3JM6AOOA@mail.gmail.com> (raw)
In-Reply-To: <CAJ7c6TOramr1UTLcyB128LWMqita1Y7=arq3KHaU=qikf5yKOQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAJ7c6TOramr1UTLcyB128LWMqita1Y7=arq3KHaU=qikf5yKOQ@mail.gmail.com>

Hi,

> > I'm writing to propose adding two new built-in functions to PostgreSQL that provide compact UUID encoding using the base32hex format.
>
> Firstly, cc:'ing a few dozens of people is not the best way to get
> attention to your patch. Please don't do this.
>
> [...]

I checked pgsql-hackers@ archive [1] and if I understand correctly
Sergey is not on the mailing list. So people that were not cc:'ed
didn't receive his e-mail. I attached the original text for those
interested and also for history.

Sergey, please make sure you are subscribed to the mailing list [2].

[1]: https://www.postgresql.org/message-id/CAJ7c6TOramr1UTLcyB128LWMqita1Y7%3Darq3KHaU%3Dqikf5yKOQ%40mail...
[2]: https://www.postgresql.org/list/

-- 
Best regards,
Aleksander Alekseev

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

Attachments:

  [text/plain] hi-hackers.txt (2.2K, 2-hi-hackers.txt)
  download | inline:
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

view thread (62+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
  In-Reply-To: <CAJ7c6TNOWMOsUPDHHDs=D-0T_c2XxDuTcrD7XwFNuz3JM6AOOA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox