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

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 23 Oct 2025 12:55:01 +0300

Hi Sergey,

> 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.

Secondly, in order to propose a patch please use `git format-patch`
and send it as an attachment. Then register it on the nearest open
commitfest [1].

The interface you are proposing is ugly and is not composable. The
right way of doing this IMO would be:

1. Implement uuid -> bytea and bytea -> uuid casting
2. Implement encode(bytea, 'base32') and decode(text, 'base32')

So the overall interface should be like this:

SELECT encode(uuidv7() :: bytea, 'base32');

The value of converting uuid to base32 is not obvious though, so I
would recommend explaining it in more detail. Consider starting a new
thread for each separate patch.

[1]: https://commitfest.postgresql.org/
-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 23 Oct 2025 12:34:43 +0000 (UTC)

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

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 23 Oct 2025 18:06:43 +0500

Hello!

> On 23 Oct 2025, at 14:55, Aleksander Alekseev <[email-masked]> wrote:
> 

I think it's not about review yet, but more of a discussing viability and general approach.
The code itself is trivial in this case.

My first reaction was very skeptical too. Yes, this representation (28V4APV8JC9D792M89J185Q000) seems more developer-friendly than default (123e4567-e89b-12d3-a456-426614174000). But why should we bother with propagating one data format over another?

Yet, this format is RFC-blessed. It makes sense to consider providing an alternative to unfriendly format.

> SELECT encode(uuidv7() :: bytea, 'base32');

That's an excellent feedback! Would such conversion be idiomatic for Postgres users?
Are there any other alternative approaches?

> would recommend explaining it in more detail.

Yes, and maybe some examples of other systems that adopted this format would be handy too. Sergey, can you, please, extend reasoning why this particular format is prominent? RFC 4648 describes a bunch of formats.

> Consider starting a new

I think this thread is fine for discussing.

Thank you!

Best regards, Andrey Borodin.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Thu, 23 Oct 2025 16:10:04 +0200

> Are there any other alternative approaches?

Agreed that extending the encode function is the way to go. An example
of that is the recently added support for base64url:
https://git.postgresql.org/cgit/postgresql.git/commit/?h=REL_18_0&id=e1d917182c1953b16b32a39ed2fe38e3d0823047

> Yes, and maybe some examples of other systems that adopted this format would be handy too. Sergey, can you, please, extend reasoning why this particular format is prominent? RFC 4648 describes a bu…

I've definitely used base32 to encode uuids myself. The primary
benefit being shorter strings, while still being able to spell them
out by voice to people without having to specify whether a letter is
upper or lowercase.

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 23 Oct 2025 18:08:29 +0300

Hi,

> [...]

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.gmail.com
[2]: https://www.postgresql.org/list/

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 23 Oct 2025 17:34:13 +0000 (UTC)

>> The value of converting uuid to base32 is not obvious though, so I>> would recommend explaining it in more detail.

> Yes, and maybe some examples of other systems that adopted this format would be handy too.

DNSSEC (https://en.wikipedia.org/wiki/Domain_Name_System_Security_Extensions)
many encoders and decoders
> Sergey, can you, please, extend reasoning why this particular format is prominent? RFC 4648 describes a bunch of formats.

> Best regards, Andrey Borodin.

Base32hex:1. Preserves sort order (unlike base64)2. Compact3. Standardized and therefore implemented consistently everywhere4. Implemented in many programming languages' standard libraries5. Does not require specifying character case during dictation6. Has simple and high-performance encoding and decoding algorithms (necessary for system integration using JSON)
The only compact text encoding eliminates the problem of incompatibility. The authors and contributors of RFC 9562 were categorically against having multiple encodings for UUIDs. They wanted to have only one compact, sort-order-preserving text encoding. For compatibility, they added the canonical UUID format. Due to time constraints, the compact encoding was not included in RFC 9562.
In databases, UUIDs should preferably be stored in binary format (the UUID type in PostgreSQL) according to RFC 9562.
Intermediate formats (bytea) reduce performance, which is the very reason we even abandoned the more compact base36 encoding.

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 23 Oct 2025 14:00:50 -0700

On Thu, Oct 23, 2025 at 10:34 AM Sergey Prokhorenko
<[email-masked]> wrote:
> Base32hex:
> 1. Preserves sort order (unlike base64)
> 2. Compact
> 3. Standardized and therefore implemented consistently everywhere
> 4. Implemented in many programming languages' standard libraries
> 5. Does not require specifying character case during dictation
> 6. Has simple and high-performance encoding and decoding algorithms (necessary for system integration using JSON)
>

Given that what uuid_to_base32hex() actually does is encoding the
input UUID,  I find that it could be confusing if we have a similar
function other than encode() function. Also, we could end up
introducing as many encoding and decoding functions dedicated for UUID
as we want to support encoding methods, bloating the functions.

So as the first step, +1 for supporting base32hex for encode() and
decode() functions and supporting the UUID <-> bytea conversion. I
believe it would cover most use cases and the cost of UUID <-> bytea
conversion is negligible.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 23 Oct 2025 22:45:48 +0000 (UTC)

> Given that what uuid_to_base32hex() actually does is encoding theinput UUID,  I find that it could be confusing if we have a similar

Masahiko,
I see you're in favor of base32hex encoding. That's great!
Your arguments make sense, and I generally support enhancing the standard encode() and decode() functions to handle base32hex. It seems like the right approach from a developer experience standpoint.
However, I'm unclear about some implementation aspects. Why add conversions between UUID and bytea data types? Wouldn't that require creating dedicated UUID <-> bytea conversion functions? Instead, could we implement encode() as polymorphic to handle UUID type inputs directly? For decode(), we'd need  some way (a parameter?) to specify the UUID output type instead of bytea. Another option would be automatic type casting when inserting bytea data into UUID columns. Neither an extra parameter nor additional type casting seems ideal to me, though I don't have better alternatives. But actually, for a short UUID text encoding to succeed, it's more important that it becomes the single, de facto standard. We should avoid supporting multiple encodings, just as the authors and contributors of RFC 9562 did: https://github.com/uuid6/new-uuid-encoding-techniques-ietf-draft/discussions/17#discussioncomment-10614817    Therefore, whenever possible, encode() and decode() should support just one UUID text encoding, namely base32hex.
Best regards,Sergey Prokhorenko

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 23 Oct 2025 21:57:55 -0700

On Thu, Oct 23, 2025 at 3:46 PM Sergey Prokhorenko
<[email-masked]> wrote:
> However, I'm unclear about some implementation aspects. Why add conversions between UUID and bytea data types? Wouldn't that require creating dedicated UUID <-> bytea conversion functions? Instead, could we implement encode() as polymorphic to handle UUID type inputs directly? For decode(), we'd need  some way (a parameter?) to specify the UUID output type instead of bytea. Another option would be automatic type casting when inserting bytea data into UUID columns. Neither an extra parameter nor additional type casting seems ideal to me, though I don't have better alternatives.

While we can implement something like decode(uuid, text), I don't
think we can implement decode() in the way you proposed unless I'm
missing something.

I think the conversion support between UUID and bytea is useful in
general, not limited to encode()/decode() support. And users would be
able to create wrapper functions if they don't want to add casting for
every encode() and decode() calls. For example,

create function uuid_to_base32(uuid) returns text language sql immutable strict
begin atomic
    select encode($1::bytea, 'base32hex');
end;

Since such functions are inlineable, the different between executing
encode(uuid_data::bytea, 'base32hex') and encode(uuid_data,
'base32hex') would only be the conversion; one palloc and one memcpy.

> But actually, for a short UUID text encoding to succeed, it's more important that it becomes the single, de facto standard. We should avoid supporting multiple encodings, just as the authors and contributors of RFC 9562 did: https://github.com/uuid6/new-uuid-encoding-techniques-ietf-draft/discussions/17#discussioncomment-10614817    Therefore, whenever possible, encode() and decode() should support just one UUID text encoding, namely base32hex.

I guess it's ultimately the developer's choice, no? For example, if
they are using multiple databases (or data processing platforms) in
their system and 'hex' is the only encoding that all components can
encode and decode, they might choose 'hex' encoding.

Regards,

---

From: Sergey Prokhorenko <[email-masked]>
Date: Fri, 24 Oct 2025 07:16:41 +0000 (UTC)

Regards,

____________________________________________

Masahiko,
Developers will still be able to use the long canonical 'hex' UUID format for compatibility. But the short format is not a developer choice, but a convention. We mustn't allow a situation where 25% of systems use base32hex, 25% use Crocksford's Base32, 25% use base36, and 25% even use erroneously sorted base64. That's a very real nightmare. You, too, have every reason not to want to increase the number of built-in functions in PostgreSQL.
 But here is a solution that I hope will satisfy everyone:

encode('019535d9-3df7-79fb-b466-​fa907fa17f9e', 'uuid_to_base32hex') -> 06AJBM9TUTSVND36VA87V8BVJOdecode('06AJBM9TUTSVND36VA87V8BVJO', 'base32hex_to_uuid') -> 019535d9-3df7-79fb-b466-​fa907fa17f9e
I don't see any real business need for UUID <-> bytea conversions.
Best regards,Sergey Prokhorenko

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 24 Oct 2025 11:23:23 -0700

On Fri, Oct 24, 2025 at 12:17 AM Sergey Prokhorenko
<[email-masked]> wrote:
> encode('019535d9-3df7-79fb-b466-fa907fa17f9e', 'uuid_to_base32hex') -> 06AJBM9TUTSVND36VA87V8BVJO

Does it mean the first argument is uuid type data and when
'uuid_to_base32hex' is specified as the format the function requires a
uuid data at the first argument? I could not understand the difference
between specifying 'based32hex' and 'uuid_to_base32hex' when encoding
UUID data with base32hex encoding.

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

Suppose that the decode() takes text data at the first argument and
returns UUID data, the function signature would be decode(text, text)
-> uuid. But we cannot create two functions that have the same name
and the same argument types.

Regards,

---

From: Sergey Prokhorenko <[email-masked]>
Date: Fri, 24 Oct 2025 22:39:45 +0000 (UTC)

On Friday 24 October 2025 at 09:24:15 pm GMT+3, Masahiko Sawada <[email-masked]> wrote:  

> uuid data at the first argument?

Yes, that's right.PostgreSQL will automatically cast the string '019535d9-3df7-79fb-b466-fa907fa17f9e' to the uuid type, since the format is correct.

> I could not understand the difference

1. Specifying 'based32hex' in encode function means the first parameter is of bytea type as usual. 

But specifying 'uuid_to_base32hex' means the first parameter is of uuid type.

2. The encode function does not yet support format based32hex. Therefore, it is not known whether padding ===== should be used.
But padding ===== is not used when specifying 'uuid_to_base32hex'.

> and the same argument types.
Yes, you're right. This is a problem that can't be solved without composite return values. We clearly took the wrong approach by coupling UUID conversion with encode/decode functions, which only apply to bytea. UUID and bytea are fundamentally different data types. Meanwhile, PostgreSQL has over 30 other type conversion functions that deal with other data types. For example, array_to_string, string_to_array, jsonb_to_record, to_char, to_timestamp, and to_hex. In this situation, the best solution would be to revert to the original uuid_to_base32hex() and base32hex_to_uuid() functions rather than deal with type incompatibility issues.

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 24 Oct 2025 16:31:25 -0700

On Fri, Oct 24, 2025 at 3:42 PM Sergey Prokhorenko
<[email-masked]> wrote:
> Yes, that's right.
> PostgreSQL will automatically cast the string '019535d9-3df7-79fb-b466-fa907fa17f9e' to the uuid type, since the format is correct.
>
>

I think that type conversions and data encodings serve different
purposes. Type conversions express semantic transformations between
data types (e.g., text -> timestamp, jsonb -> record), while encodings
are simply representations of binary data as text. For the latter,
PostgreSQL already provides a well-defined abstraction through
encode()/decode(). Mixing encoding logic with type-specific
conversions would blur that boundary.

Also, if we start adding dedicated functions for each supported
encoding (uuid_to_base32hex, uuid_to_hex etc.), the number of
functions could easily multiply. That’s exactly what encode() and
decode() were designed to avoid.

While I agree that base32hex should be the recommended, I'm really not
sure it's a good design that PostgreSQL core should enforce it as the
only built-in method. It seems better to me to provide flexible
primitives, encode()/decode() plus UUID <-> bytea casts, and document
base32hex as the canonical convention (if necessary). Or providing
'uuid_encode(uuid, format text) -> text' and 'uuid_decode(text, format
text) -> uuid' might make sense too, but I'm not sure. I'd like to
hear opinions from other hackers too.

Regards,

---

From: Andrey Borodin <[email-masked]>
Date: Sat, 25 Oct 2025 23:07:10 +0500

> On 25 Oct 2025, at 04:31, Masahiko Sawada <[email-masked]> wrote:
> 
> Or providing
> text) -> uuid' might make sense too, but I'm not sure.

I like the idea, so I drafted a prototype for discussion.
Though I do not see what else methods should be provided along with added one...

---

From: Sergey Prokhorenko <[email-masked]>
Date: Sat, 25 Oct 2025 19:15:48 +0000 (UTC)

> On 25 Oct 2025, at 04:31, Masahiko Sawada <[email-masked]> wrote:> 
> Or providing
> On Saturday 25 October 2025 at 09:07:39 pm GMT+3, Andrey Borodin <[email-masked]> wrote:

> Best regards, Andrey Borodin.

If base32hex becomes the default string representation for UUIDs in PostgreSQL, then the canonical UUID string representation may be added into these functions for backward compatibility.
Best regards,
Sergey Prokhorenko

---

From: Aleksander Alekseev <[email-masked]>
Date: Mon, 27 Oct 2025 13:23:27 +0300

Hi,

> Though I do not see what else methods should be provided along with added one...

I see no reason why we should forbid the use of base32 encoding with
bytea. Or have different functions for this e.g. uuid_encode() and
encode(). To me it looks like a poor API design.

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Mon, 27 Oct 2025 14:03:09 +0000 (UTC)

Hi,

-- 
Best regards,
Aleksander Alekseev

It seems that bytea is your personal interest, since you continue to impose your bytea when a better solution has already been found with uuid_encode() and uuid_decode().

The bytea proposal has a lot of drawbacks:

1. It requires unnecessary casting in addition to encoding/decoding. This complicates the interface and creates unnecessary cognitive load on developers. It also creates additional CPU load, although perhaps only a small amount.

2. The encoding function encourages developers to use the slightly more compact base64 encoding (see https://www.postgresql.org/docs/current/functions-binarystring.html), which doesn't preserve sort order, isn't URL-safe, is case-sensitive, and requires specifying the case of letters when dictating. This also creates a serious problem of incompatibility between UUID encodings.

3. The hashing functions used by bytea create a temptation to implement popular, idiotic ideas for hashing UUIDs to obscure their creation date and to hide internal keys from clients.

4. Other various functions for bytea allow the construction of Frankenstein identifiers that compete with UUIDv7, which could negatively impact the reputation of UUIDs.

The bytea type has nothing in common with the uuid type other than the binary encoding. Therefore, the bytea <-> uuid cast can only encourage abuse and errors, creating the illusion of unlimited developer power.

The bytea proposal has no merit whatsoever. It's the worst, most insafe, and most harmful design, undermining efforts to widely adopt UUIDv7 and improve PostgreSQL.

---

From: Aleksander Alekseev <[email-masked]>
Date: Mon, 27 Oct 2025 17:37:37 +0300

Hi Sergey,

> hear opinions from other hackers too.

I merely shared my personal opinion on why I think this is a bad idea.
Let's see what other people think.

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Mon, 27 Oct 2025 15:33:31 +0000 (UTC)

Hi Sergey,

-- 
Best regards,
Aleksander Alekseev

_____________________
You didn't give any arguments in favor of your opinion

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 27 Oct 2025 13:09:43 -0700

> Though I do not see what else methods should be provided along with added one...

Thank you for drafting the patch! But I find it potentially confusing
to have different encoding methods for bytea and UUID types. I don't
see a compelling reason why the core should support base32hex
exclusively for the UUID data type, nor why base32hex should be the
only encoding method that the core provides for UUIDs (while we can
use it by default).

If we implement uuid_encode() and uuid_decode(), we might end up
creating similar encoding and decoding functions for other data types
as well, which doesn't seem like the best approach. I still believe
that extending the existing encode() and decode() functions is a
better starting point.

Regards,

---

From: Sergey Prokhorenko <[email-masked]>
Date: Mon, 27 Oct 2025 22:36:51 +0000 (UTC)

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
________________________________________________

Masahiko,
 I wanted to highlight an important discussion among the authors and contributors of RFC 9562 regarding UUID text encoding:
https://github.com/uuid6/new-uuid-encoding-techniques-ietf-draft/discussions/17#discussioncomment-10614817
 The RFC 9562 authors and contributors reached consensus that standardizing an alternate short text format for UUIDs is important. While the community debated between base32hex (RFC 4648) and Crockford's Base32, both were recognized for preserving lexicographical sort order, a critical property for database primary keys and URL-safe identifiers. Time constraints prevented inclusion in RFC 9562, but the discussion established that base32hex is the existing standard format already defined in RFC 4648, Section 7, specifically designed for sort-preserving encoding.

This context is crucial because it underscores that the uuid type, as a first-class concept, deserves its own standardized text encoding.

Regarding the proposal to couple UUID encoding with the bytea type through encode()/decode() functions: I understand the appeal of reusing existing infrastructure, but this creates a conceptual mismatch. UUID is a distinct semantic type in PostgreSQL, not merely binary data. The bytea type has existed for decades without base32hex encoding, and that's worked fine, because bytea represents arbitrary binary data, not universally unique identifiers with specific structural properties and needs.
Consider PostgreSQL's own design philosophy. The documentation states:
"9.5. Binary String Functions and Operators  This section describes functions and operators for examining and manipulating binary strings, that is values of type bytea. Many of these are equivalent, in purpose and syntax, to the text-string functions described in the previous section."
 PostgreSQL maintains parallel function sets for text strings and bytea precisely because they serve different purposes, despite the implementation overhead. The uuid type deserves the same treatment: it's not just another binary blob, but a type with specific semantics (uniqueness, version bits, variant encoding) and use cases (distributed identifiers, sortable keys, URL-safe representations).
Why should uuid be treated as a second-class citizen and forced through bytea conversion, when text and bytea each have their own dedicated function families?
You've been very careful in your previous arguments to separate data type conversion from encoding/decoding operations. I appreciate that rigor. However, the current proposal to route UUID encoding through bytea contradicts that principle. It merges two fundamentally different data types for convenience rather than correctness.
 If someone wants to add base32hex encoding/decoding to bytea for general binary data operations, that's a worthwhile but separate discussion. The uuid type, however, needs native base32hex support to fulfill its role as a first-class PostgreSQL type with a standardized compact text representation, as recommended by the RFC 9562 community.

I would value your thoughts on these arguments.

Best regards,
Sergey Prokhorenko

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 28 Oct 2025 10:30:04 +0100

First of all, I'm definitely a proponent of being able to encode UUIDs
using base32hex in Postgres.

> https://github.com/uuid6/new-uuid-encoding-techniques-ietf-draft/discussions/17#discussioncomment-10614817

I think a very important thing to note here is that this is a github
discussion, not an officially accepted RFC. I think if it was an
officially accepted RFC on how to encode UUIDs then you would have a
lot less pushback here. Right now your emails mostly read like you
want to push your preferential format, while essentially disallowing
other encodings. While base32hex seems like a good choice for UUIDv7 I
see no reason to give it preferential treatment at this point in time.
crockford base32 seems just as valid. And e.g. base64url[1] seems
totally fine for UUID versions that have no inherent ordering like
UUIDv4. And if someone comes up with a base64urlhex format you could
have even shorter bit still sortable UUIDs at the expense of
legibility.

The main reason why a specific encoding should receive preferential
treatment in Postgres, would be if it was standardized, as that would
help with interoperability. At this point in time there's no such
standard (not even a draft), so forcing an explicit encoding will
actually reduce interoperability, because people already encode their
UUIDs in various different forms.

> but the discussion established that base32hex is the existing standard format already defined in RFC 4648, Section 7, specifically designed for sort-preserving encoding.

You even reach a similar conclusion here: not choosing crockford
base32, purely because it does not have an official RFC.

> This context is crucial because it underscores that the uuid type, as a first-class concept, deserves its own standardized text encoding.

It already has! The standard text encoding is defined in RFC 4122.
That's why postgres displays it as such when encoding to text.

> Regarding the proposal to couple UUID encoding with the bytea type through encode()/decode() functions: I understand the appeal of reusing existing infrastructure, but this creates a conceptual mis…

I think by far the first step is to make the encoding of UUIDs in
different formats possible in Postgres. The way to do so with the
least API impact (and thus as you noticed, least pushback), would be
to add base32hex to the list of encoding formats in the encode/decode
functions. Then combining that with UUID <-> bytea casting (which also
seems totally reasonable functionality to me), would give you the
functionality (but not the defaults you want).

In a follow up patch I would personally be fine making the API to
encode UUIDs a bit more friendly. In particular, adding an overload to
the encode function that takes a UUID instead of a bytea seems
reasonable to me, i.e. encode(id uuid, format text) -> text

I'm currently less convinced about a decode_uuid function though. I
think some perf argument (including some benchmarks) would need to be
made to convince me of its usefulness. Because purely from an API
friendliness lens, I feel like decode('...', 'base32hex)::uuid and
decode_uuid('...', 'base32hex') rank basically the same.

Once/if an accepted RFC actually defines a default shorter encoding
for UUIDs we could I would definitely be in favor of adding a
decode_uuid function with the default encoding configured as a default
argument. As well as adding the default argument to the uuid encode
overload function.

---

From: Dagfinn Ilmari Mannsåker <[email-masked]>
Date: Tue, 28 Oct 2025 11:53:51 +0000

Jelte Fennema-Nio <[email-masked]> writes:

>> Regarding the proposal to couple UUID encoding with the bytea type
>> through encode()/decode() functions: I understand the appeal of
>> reusing existing infrastructure, but this creates a conceptual
>> mismatch. UUID is a distinct semantic type in PostgreSQL, not merely
>> binary data. The bytea type has existed for decades without base32hex
>> encoding, and that's worked fine, because bytea represents arbitrary
>> binary data, not universally unique identifiers with specific
>> structural properties and needs.
>

+1 for adding casts.

> reasonable to me, i.e. encode(id uuid, format text) -> text

Yeah, this works for encode, but not decode, since the argment types are
the same (text, text) in both cases.

> decode_uuid('...', 'base32hex') rank basically the same.

Agreed.

> argument. As well as adding the default argument to the uuid encode

If it's just one new form, do we need a separate decode function?  Could
we not just make uuid_in() accept both forms (they're easily
distinguishable by length), like bytea_in accepts both the old escape
format and the new hex format?

And if the new format becomes the standard and want to change the
default output format, we would need a GUC like bytea_output anyway, to
let users control when to make the change.

- ilmari

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 28 Oct 2025 13:24:49 +0100

> let users control when to make the change.

Agreed to both of those. This seems too far out to spend much time
discussing now.

---

From: Sergey Prokhorenko <[email-masked]>
Date: Tue, 28 Oct 2025 14:28:54 +0000 (UTC)

First of all, I'm definitely a proponent of being able to encode UUIDsusing base32hex in Postgres.

______________________________________________________________________________________________

Hi Jelte,
I agree with your points.
I believe we should put the discussion about compact UUID text encoding in PostgreSQL on hold for now. None of the proposed solutions has sufficient unconditional support from the participants. It makes sense to pause this discussion for more in-depth exploration to try and reach a consensus.
Jelte, I particularly liked your idea of a new, dedicated, standardized encoding for UUIDs, base64urlhex, and dedicated encoding/decoding functions for this encoding in PostgreSQL. I will try to develop such an encoding and submit it for discussion. I suggest calling it base64uuid.
My current attempt to establish base32hex as a de facto standard (even prior to an RFC) was unsuccessful. However, I remain convinced, like the authors of RFC 9562, that there should be only one standard compact encoding for UUIDs. Therefore, we must continue efforts to standardize such an encoding.
As for Crockford's Base32, it was rejected because of a lack of support in standard programming language libraries. Otherwise, it's just as good as base32hex.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 28 Oct 2025 15:39:26 +0100

On Tue, 28 Oct 2025 at 15:29, Sergey Prokhorenko
<[email-masked]> wrote:
> I believe we should put the discussion about compact UUID text encoding in PostgreSQL on hold for now.

I agree with respect to the dedicated encode_uuid and decode_uuid functions.

But I think having postgres support base32hex in encode/decode for
bytea seems like a sensible thing in any case. As well as conversion
between UUID and bytea. If someone wants to create patches for that,
it seems like there'd be enough support for those to get them merged.

---

From: Dagfinn Ilmari Mannsåker <[email-masked]>
Date: Tue, 28 Oct 2025 16:41:37 +0000

Dagfinn Ilmari Mannsåker <[email-masked]> writes:

>> Then combining that with UUID <-> bytea casting (which also

Here's a patch for that.  I'm not 100% confident about the error code
for invalid length, but that was the closest one I could find in
errcodes.txt.

I might do the base32hex encode/decode later, unless someone else beats
me to it.

- ilmari

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 28 Oct 2025 11:44:12 -0700

> for invalid length, but that was the closest one I could find in

Thank you for the patch. I'll review it.

> I might do the base32hex encode/decode later, unless someone else beats

Andrey has shared his patch for base32hex support before[1]. While it
needs to be updated, it seems to implement sufficient function.

Regards,

[1] https://www.postgresql.org/message-id/6F76FA61-E2DC-44EF-9504-889D9BDB4EBD%40yandex-team.ru

---

From: Sergey Prokhorenko <[email-masked]>
Date: Tue, 28 Oct 2025 21:56:32 +0000 (UTC)

____________________________________________________

Hi Jelte,

Here's a project for a compact, sortable text encoding Base64UUID specifically for UUIDs, the idea for which you submitted ("base64urlhex"):
https://github.com/sergeyprokhorenko/Base64UUID
Where's the best place to discuss this: in this thread or starting a new one?

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Wed, 29 Oct 2025 00:35:28 +0100

On Tue, 28 Oct 2025 at 17:41, Dagfinn Ilmari Mannsåker
<[email-masked]> wrote:
> Here's a patch for that.

Looks good to me. Maybe add a test where not every byte is the same though.

> I'm not 100% confident about the error code

The errorcode you chose seems acceptable to me, but I think a slightly
more fitting option would be ERRCODE_INVALID_BINARY_REPRESENTATION.
Error codes in postgres are pretty arbitrary though, so either seems
fine to me.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Wed, 29 Oct 2025 00:44:00 +0100

> Where's the best place to discuss this: in this thread or starting a new one?

I don't think core Postgres is the place where experimental encodings
should be discussed and tried (it would fit great in an extension
though). It sounds more like you should create an RFC or ask for
feedback from some of the other contributors to the UUIDv7 RFC.

---

From: Dagfinn Ilmari Mannsåker <[email-masked]>
Date: Wed, 29 Oct 2025 10:51:56 +0000

> Looks good to me. Maybe add a test where not every byte is the same though.

Good point. I've replaced them with two randomly generated ones.

> Error codes in postgres are pretty arbitrary though, so either seems

That does seem like a better fit. It's used mainly in recv functions,
which this basically is (but user-callable).

Updated patch attaced.

- ilmari

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 29 Oct 2025 14:31:29 +0300

Hi,

Thanks for the patch.

> which this basically is (but user-callable).

Perhaps bytea_uuid() should check the UUID format. Consider the
following example:

SELECT uuid_extract_version('\x019a2f859cedffffb99d9c55044a2563'::bytea::uuid);
 uuid_extract_version
----------------------
                   15

There is no UUID version 15 according to RFC 9562, and the
documentation for uuid_extract_version() says:

"""
Extracts the version from a UUID of the variant described by RFC 9562.
For other variants, this function returns null. For example, for a
UUID generated by gen_random_uuid, this function will return 4.
"""

If I read this correctly, either bytea_uuid() should reject this, or
uuid_extract_version() should be modified to return NULL, or the
documentation for uuid_extract_version() should be altered.

-- 
Best regards,
Aleksander Alekseev

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 29 Oct 2025 14:57:59 +0300

Hi,

> SELECT uuid_extract_version('\x019a2f859cedffffb99d9c55044a2563'::bytea::uuid);

On the flip side we allow this:

=# select '019a2f85-9ced-ffff-b99d-9c55044a2563' :: uuid;
                 uuid
--------------------------------------
 019a2f85-9ced-ffff-b99d-9c55044a2563

So I guess the patch is fine.

-- 
Best regards,
Aleksander Alekseev

---

From: Dagfinn Ilmari Mannsåker <[email-masked]>
Date: Wed, 29 Oct 2025 12:04:24 +0000

> SELECT uuid_extract_version('\x019a2f859cedffffb99d9c55044a2563'::bytea::uuid);

The UUID input function doesn't do any such validation, so I don't see
why the cast should behave any differently:

# select '019a2f859cedffffb99d9c55044a2563'::uuid;
┌──────────────────────────────────────┐
│                 uuid                 │
├──────────────────────────────────────┤
│ 019a2f85-9ced-ffff-b99d-9c55044a2563 │
└──────────────────────────────────────┘

# select uuid_extract_version('019a2f859cedffffb99d9c55044a2563'::uuid);
┌──────────────────────┐
│ uuid_extract_version │
├──────────────────────┤
│                   15 │
└──────────────────────┘
(1 row)

> documentation for uuid_extract_version() says:

There's no version 15 specified _yet_.

> documentation for uuid_extract_version() should be altered.

Future RFCs could define new versions of this variant, which we should
not reject or pretend don't have a version , just because we haven't
heard of them yet.  In fact, RFC 9562 defines two new versions, v7 and
v8, which by this argument PostgreSQL versions before 18 should reject.

- ilmari

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 29 Oct 2025 16:19:08 +0400

> On 28 Oct 2025, at 22:44, Masahiko Sawada <[email-masked]> wrote:
> 

I'd propose something like attached patch. It's on top of Ilmari's v2 patch with small suggestions as a step 2.

Thanks!

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 30 Oct 2025 12:10:42 -0700

> I'd propose something like attached patch. It's on top of Ilmari's v2 patch with small suggestions as a step 2.

I've reviewed the v3 patches, and here are some review comments.

v3-0001 and v3-0002:

-                               errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
-                               errmsg("invalid uuid length"));
+                               (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
+                                errmsg("invalid length for UUID"),
+                                errdetail("Expected %d bytes, got
%d.", UUID_LEN, len)));

How about the error message like "invalid input length for type uuid"?
I think "uuid" should be lower case as it indicates PostgreSQL uuid
data type, and it's better to use %s format instead of directly
writing "uuid" (see string_to_uuid() for example).

As for the errdetail message, should we add "bytea" also after "got %d"?

---
+-- casts
+SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
+SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
+SELECT '\x1234567890abcdef'::bytea::uuid; -- error

We already have tests for casting bytes to integer data types in
strings.sql. I suggest moving the casting tests from bytea to uuid
into therel. For the uuid.sql file, we could add a test to verify that
a UUID value remains unchanged when it's cast to bytea and back to
UUID. For example,

SELECT v = v::bytea::uuid as matched FROM gen_random_uuid() v;

---
I think we should update the documentation in the uuid section about
casting data between bytea and uuid. For references, we have a similar
description for bytea and integer[1].

v3-0003:

base32hex_encode() doesn't seem to add '=' paddings, but is it
intentional? I don't see any description in RFC 4648 that we can omit
'=' paddings.

---
I think the patch should add tests not only for uuid data type but
also for general cases like other encodings.

---
In uuid.sql tests, how about adding some tests to check if base32hex
maintains the sortability of UUIDv7 data?

---
I would suggest registering the patches to the next commit fest if not yet.

Regards,

[1] https://www.postgresql.org/docs/devel/functions-binarystring.html

---

From: Илья Чердаков<[email-masked]>
Date: Thu, 20 Nov 2025 12:46:43 +0700

> description for bytea and integer[1].
Greetings!
I briefly tested the patched version of v3. The implemented
functionality works correctly.

---
You can also add a case with the error from v3-0002
"invalid base32hex end sequence" to the tests :

+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                 errmsg("invalid base32hex end sequence"),
+                 errhint("Input data has non-zero padding bits.")));

---
I agree with Masahiko Sawada; information about conversions
should be added to the documentation.

-- 
Best regards,
Ilya Cherdakov, PostgresPro

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 13 Mar 2026 21:10:31 -0700

On Wed, Feb 18, 2026 at 6:58 AM Aleksander Alekseev
<[email-masked]> wrote:
>
> Hi,
>
> > I only rebased v3 and improved the commit messages, but I didn't
> > account for Masahiko Sawada's feedback for 0002. Andrey, are you still
> > working on this or others can pick it up?
> >
> > The patch is not on the commitfest, so I'm about to add it.
>
> Here is patch v5 where I accounted for the previous feedback from
> Masahiko Sawada and also made some other changes, see below.
>
> Makes sense. Fixed.
> You probably meant "got %d bytes", not "got %d bytea". I believe the
> current message is fine, but maybe native speakers will correct us.
>
> > into therel.
> I disagree on the grounds that there are zero tests related to UUID in
> strings.sql; uuid.sql is a more appropriate place for these tests IMO.
> However if someone seconds the idea we can easily move the tests at
> any time.
>
> > For the uuid.sql file, we could add a test to verify that
> Good point. Added.
> You are right, both base32 and base32hex should add paddings;
> substring() can be used if necessary. Fixed.
>
> Yes, and the good place for these tests would be closer to other tests
> for encode() and decode() i.e. strings.sql. Fixed.
>
> While working on it I noticed some inconsistencies between base32hex
> implementation and our current implementation of base64. As an
> example, we don't allow `=` input:
>
> ```
> =# SELECT decode('=', 'base64');
> ERROR:  unexpected "=" while decoding base64 sequence
> ```
>
> ... while base32hex did. I fixed such inconsistencies too.
>
> Agree. Added.
> Fair point. Fixed.

Thank you for updating the patch!

I've reviewed both patches and have some comments.

* 0001 patch

+       ereport(ERROR,
+               (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
+                errmsg("invalid input length for type %s", "uuid"),
+                errdetail("Expected %d bytes, got %d.", UUID_LEN, len)));

I think we need to handle plural and singular forms depending on the
value. Or we can change it to "Expected size %d, got %d".

* 0002 patch:

                errhint("Valid encodings are \"%s\", \"%s\", \"%s\",
and \"%s\".",
                        "base64", "base64url", "escape", "hex")));

We need to add 'base32hex' here.

---
+                   ereport(ERROR,
+                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                            errmsg("unexpected \"=\" while decoding
%s sequence",
+                                   "base32hex")));

I think we can directly write 'base32hex' in the error message.

---
+   /* Verify no extra bits remain (padding bits should be zero) */
+   if (bits_in_buffer > 0 && (bits_buffer & ((1ULL << bits_in_buffer)
- 1)) != 0)
+       ereport(ERROR,
+               (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                errmsg("invalid base32hex end sequence"),
+                errhint("Input data has non-zero padding bits.")));

This code checks if the remaining bits of the input data are all zero.
IIUC we don't have a similar check for base64 and base64url. For
instance, the following input data is accepted:

=# select decode('AB', 'base64');
 decode
--------
 \x00
(1 row)

I think it's better to have consistent behavior across our encoding.

I've attached a patch for the 0002 patch part that fixes the above
points (except for the last point) and has some minor fixes as well.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index 51be0463eec..7aa3805f1ec 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -815,14 +815,16 @@
        The <literal>base32hex</literal> format is that of
        <ulink url="https://datatracker.ietf.org/doc/html/rfc4648#section-7">
        RFC 4648 Section 7</ulink>.  It uses the extended hex alphabet
-       (0-9, A-V) which preserves sort order when encoding binary data.
-       The <function>encode</function> function produces padded output,
-       while <function>decode</function> accepts both padded and unpadded
-       input. Decoding is case-insensitive and ignores whitespace characters.
+       (<literal>0</literal>-<literal>9</literal> and
+       <literal>A</literal>-<literal>V</literal>) which preserves sort order
+       when encoding binary data. The <function>encode</function> function
+       produces padded output, while <function>decode</function> accepts both
+       padded and unpadded input. Decoding is case-insensitive and ignores
+       whitespace characters.
       </para>
       <para>
        This format can be used for encoding UUIDs in a compact, sortable format:
-       <literal>substring(encode(uuid_value :: bytea, 'base32hex') from 1 for 26)</literal>
+       <literal>substring(encode(uuid_value::bytea, 'base32hex') FROM 1 FOR 26)</literal>
        produces a 26-character string compared to the standard 36-character
        UUID representation.
       </para>
diff --git a/src/backend/utils/adt/encode.c b/src/backend/utils/adt/encode.c
index 6153a5cb5f2..ea11bc3f3b5 100644
--- a/src/backend/utils/adt/encode.c
+++ b/src/backend/utils/adt/encode.c
@@ -65,8 +65,8 @@ binary_encode(PG_FUNCTION_ARGS)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("unrecognized encoding: \"%s\"", namebuf),
-				 errhint("Valid encodings are \"%s\", \"%s\", \"%s\", and \"%s\".",
-						 "base64", "base64url", "escape", "hex")));
+				 errhint("Valid encodings are \"%s\", \"%s\", \"%s\", \"%s\", and \"%s\".",
+						 "base64", "base64url", "base32hex", "escape", "hex")));
 
 	dataptr = VARDATA_ANY(data);
 	datalen = VARSIZE_ANY_EXHDR(data);
@@ -115,8 +115,8 @@ binary_decode(PG_FUNCTION_ARGS)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("unrecognized encoding: \"%s\"", namebuf),
-				 errhint("Valid encodings are \"%s\", \"%s\", \"%s\", and \"%s\".",
-						 "base64", "base64url", "escape", "hex")));
+				 errhint("Valid encodings are \"%s\", \"%s\", \"%s\", \"%s\", and \"%s\".",
+						 "base64", "base64url", "base32hex", "escape", "hex")));
 
 	dataptr = VARDATA_ANY(data);
 	datalen = VARSIZE_ANY_EXHDR(data);
@@ -873,15 +873,11 @@ base32hex_encode(const char *src, size_t srclen, char *dst)
 
 	/* Handle remaining bits (if any) */
 	if (bits_in_buffer > 0)
-	{
 		dst[output_pos++] = base32hex_table[(bits_buffer << (5 - bits_in_buffer)) & 0x1F];
-	}
 
 	/* Add padding to make length a multiple of 8 (per RFC 4648) */
 	while (output_pos % 8 != 0)
-	{
 		dst[output_pos++] = '=';
-	}
 
 	return output_pos;
 }
@@ -909,8 +905,8 @@ base32hex_decode(const char *src, size_t srclen, char *dst)
 		if (c == '=')
 		{
 			/*
-			 * Padding is only valid at positions 2, 4, 5, or 7 within an
-			 * 8-character group (corresponding to 1, 2, 3, or 4 input bytes).
+			 * The first padding is only valid at positions 2, 4, 5, or 7 within
+			 * an 8-character group (corresponding to 1, 2, 3, or 4 input bytes).
 			 * We only check the position for the first '=' character.
 			 */
 			if (!end)
@@ -918,8 +914,7 @@ base32hex_decode(const char *src, size_t srclen, char *dst)
 				if (pos != 2 && pos != 4 && pos != 5 && pos != 7)
 					ereport(ERROR,
 							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-							 errmsg("unexpected \"=\" while decoding %s sequence",
-									"base32hex")));
+							 errmsg("unexpected \"=\" while decoding base32hex sequence")));
 				end = true;
 			}
 			pos++;
@@ -930,9 +925,8 @@ base32hex_decode(const char *src, size_t srclen, char *dst)
 		if (end)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("invalid symbol \"%.*s\" found while decoding %s sequence",
-							pg_mblen((const char *) &c), (const char *) &c,
-							"base32hex")));
+					 errmsg("invalid symbol \"%.*s\" found while decoding base32hex sequence",
+							pg_mblen((const char *) &c), (const char *) &c)));
 
 		/* Decode base32hex character (0-9, A-V, case-insensitive) */
 		if (c >= '0' && c <= '9')
@@ -944,9 +938,8 @@ base32hex_decode(const char *src, size_t srclen, char *dst)
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("invalid symbol \"%.*s\" found while decoding %s sequence",
-							pg_mblen((const char *) &c), (const char *) &c,
-							"base32hex")));
+					 errmsg("invalid symbol \"%.*s\" found while decoding base32hex sequence",
+							pg_mblen((const char *) &c), (const char *) &c)));
 
 		/* Add 5 bits to buffer */
 		bits_buffer = (bits_buffer << 5) | val;
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 910757537e7..53b1a14c895 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2600,10 +2600,10 @@ SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape');
 -- report an error with a hint listing valid encodings when an invalid encoding is specified
 SELECT encode('\x01'::bytea, 'invalid');  -- error
 ERROR:  unrecognized encoding: "invalid"
-HINT:  Valid encodings are "base64", "base64url", "escape", and "hex".
+HINT:  Valid encodings are "base64", "base64url", "base32hex", "escape", and "hex".
 SELECT decode('00', 'invalid');           -- error
 ERROR:  unrecognized encoding: "invalid"
-HINT:  Valid encodings are "base64", "base64url", "escape", and "hex".
+HINT:  Valid encodings are "base64", "base64url", "base32hex", "escape", and "hex".
 --
 -- base32hex encoding/decoding
 --
@@ -2698,6 +2698,12 @@ SELECT decode('24', 'base32hex');  -- OK, padding `=` are optional
  \x11
 (1 row)
 
+SELECT decode('24=======', 'base32hex');  -- OK
+ decode 
+--------
+ \x11
+(1 row)
+
 SELECT decode('24h36h2lco', 'base32hex');  -- OK, the encoding is case-insensitive
      decode     
 ----------------
@@ -2710,6 +2716,9 @@ SELECT decode('W', 'base32hex');  -- error
 ERROR:  invalid symbol "W" found while decoding base32hex sequence
 SELECT decode('24H36H0=24', 'base32hex'); -- error
 ERROR:  invalid symbol "2" found while decoding base32hex sequence
+SELECT decode('11=', 'base32hex'); -- error
+ERROR:  invalid base32hex end sequence
+HINT:  Input data has non-zero padding bits.
 --
 -- base64url encoding/decoding
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index b5237e85172..6ebc192a9b1 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -856,10 +856,12 @@ SELECT decode('24H36H2L', 'base32hex');  -- \x1122334455
 SELECT decode('24H36H2LCO======', 'base32hex');  -- \x112233445566
 
 SELECT decode('24', 'base32hex');  -- OK, padding `=` are optional
+SELECT decode('24=======', 'base32hex');  -- OK
 SELECT decode('24h36h2lco', 'base32hex');  -- OK, the encoding is case-insensitive
 SELECT decode('=', 'base32hex');  -- error
 SELECT decode('W', 'base32hex');  -- error
 SELECT decode('24H36H0=24', 'base32hex'); -- error
+SELECT decode('11=', 'base32hex'); -- error

 --

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 18 Mar 2026 14:14:27 +0300

Hi,

> points (except for the last point) and has some minor fixes as well.

Applied, thanks.

> +   if (bits_in_buffer > 0 && (bits_buffer & ((1ULL << bits_in_buffer)

Agree. Fixed.

-- 
Best regards,
Aleksander Alekseev
From bb3958f39f50ad356b9aafd2f9f460700d4d48ff Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <[email-masked]>
Date: Tue, 28 Oct 2025 16:33:17 +0000
Subject: [PATCH v6 1/2] Allow explicit casting between bytea and UUID
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This enables using encode() and decode() to convert UUIDs to and from
alternative formats, such as base64.

Author:	Dagfinn Ilmari Mannsåker <[email-masked]>
Author: Aleksander Alekseev <[email-masked]>
Reviewed-by: Masahiko Sawada <[email-masked]>
Reviewed-by: Andrey Borodin <[email-masked]>
Reviewed-by: Jelte Fennema-Nio <[email-masked]>
Discussion: https://postgr.es/m/CAJ7c6TOramr1UTLcyB128LWMqita1Y7%3Darq3KHaU%3Dqikf5yKOQ%40mail.gmail.com
---
 doc/src/sgml/datatype.sgml         | 11 +++++++++++
 src/backend/utils/adt/bytea.c      | 27 +++++++++++++++++++++++++++
 src/include/catalog/pg_cast.dat    |  6 ++++++
 src/include/catalog/pg_proc.dat    |  7 +++++++
 src/test/regress/expected/uuid.out | 22 ++++++++++++++++++++++
 src/test/regress/sql/uuid.sql      |  5 +++++
 6 files changed, 78 insertions(+)

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 3017c674040..f8264b119ab 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4439,6 +4439,17 @@ a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
     Output is always in the standard form.
    </para>
 
+   <para>
+   It is possible to cast <type>uuid</type> values to and from type
+   <type>bytea</type>. This allows using <literal>encode()</literal>
+   and <literal>decode()</literal> functions for <type>uuid</type>.
+   Some examples:
+<programlisting>
+encode('1ea3d64c-bc40-4cc3-84bb-6b11ee31e5c2'::uuid::bytea, 'base64')
+decode('HqPWTLxATMOEu2sR7jHlwg==', 'base64')::uuid
+</programlisting>
+   </para>
+
    <para>
     See <xref linkend="functions-uuid"/> for how to generate a UUID in
     <productname>PostgreSQL</productname>.
diff --git a/src/backend/utils/adt/bytea.c b/src/backend/utils/adt/bytea.c
index fd7662d41ee..4dc83671aa5 100644
--- a/src/backend/utils/adt/bytea.c
+++ b/src/backend/utils/adt/bytea.c
@@ -28,6 +28,7 @@
 #include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/sortsupport.h"
+#include "utils/uuid.h"
 #include "varatt.h"
 
 /* GUC variable */
@@ -1340,3 +1341,29 @@ int8_bytea(PG_FUNCTION_ARGS)
 {
 	return int8send(fcinfo);
 }
+
+/* Cast bytea -> uuid */
+Datum
+bytea_uuid(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+	pg_uuid_t  *uuid;
+
+	if (len != UUID_LEN)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
+				 errmsg("invalid input length for type %s", "uuid"),
+				 errdetail("Expected %d bytes, got %d.", UUID_LEN, len)));
+
+	uuid = (pg_uuid_t *) palloc(sizeof(pg_uuid_t));
+	memcpy(uuid->data, VARDATA_ANY(v), UUID_LEN);
+	PG_RETURN_UUID_P(uuid);
+}
+
+/* Cast uuid -> bytea; can just use uuid_send() */
+Datum
+uuid_bytea(PG_FUNCTION_ARGS)
+{
+	return uuid_send(fcinfo);
+}
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index 9b1cfb1b590..a7b6d812c5a 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -362,6 +362,12 @@
 { castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
   castcontext => 'e', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and uuid type
+{ castsource => 'bytea', casttarget => 'uuid', castfunc => 'uuid(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'uuid', casttarget => 'bytea', castfunc => 'bytea(uuid)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fc8d82665b8..84e7adde0e5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1208,6 +1208,13 @@
   proname => 'int8', prorettype => 'int8', proargtypes => 'bytea',
   prosrc => 'bytea_int8' },
 
+{ oid => '9880', descr => 'convert uuid to bytea',
+  proname => 'bytea', prorettype => 'bytea', proargtypes => 'uuid',
+  prosrc => 'uuid_bytea' },
+{ oid => '9881', descr => 'convert bytea to uuid',
+  proname => 'uuid', prorettype => 'uuid', proargtypes => 'bytea',
+  prosrc => 'bytea_uuid' },
+
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
   prosrc => 'hashint2' },
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 95392003b86..d157ef7d0b3 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -305,5 +305,27 @@ SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');  -- null
  
 (1 row)
 
+-- casts
+SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
+               bytea                
+------------------------------------
+ \x5b35380a714349129b55f322699c6770
+(1 row)
+
+SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
+                 uuid                 
+--------------------------------------
+ 019a2f85-9ced-7225-b99d-9c55044a2563
+(1 row)
+
+SELECT '\x1234567890abcdef'::bytea::uuid; -- error
+ERROR:  invalid input length for type uuid
+DETAIL:  Expected 16 bytes, got 8.
+SELECT v = v::bytea::uuid as matched FROM gen_random_uuid() v;
+ matched 
+---------
+ t
+(1 row)
+
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 465153a0341..f512f4dea1d 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -146,6 +146,11 @@ SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday
 SELECT uuid_extract_timestamp(gen_random_uuid());  -- null
 SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');  -- null
 
+-- casts
+SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
+SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
+SELECT '\x1234567890abcdef'::bytea::uuid; -- error
+SELECT v = v::bytea::uuid as matched FROM gen_random_uuid() v;
 
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
-- 
2.43.0

From e14d4ce24a614c313ce8af4ff0df2fcdbc4d5d08 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Wed, 29 Oct 2025 15:53:12 +0400
Subject: [PATCH v6 2/2] Add base32hex encoding support to encode() and
 decode()

Implement base32hex encoding/decoding per RFC 4648 Section 7 for
encode() and decode() functions. This encoding uses the extended hex
alphabet (0-9, A-V) which preserves sort order.

The encode() function produces padded output, while decode() accepts
both padded and unpadded input. Decoding is case-insensitive.

Author: Andrey Borodin <[email-masked]>
Author: Aleksander Alekseev <[email-masked]>
Reviewed-by: Masahiko Sawada <[email-masked]>
Suggested-by: Sergey Prokhorenko <[email-masked]>
Discussion: https://postgr.es/m/CAJ7c6TOramr1UTLcyB128LWMqita1Y7%3Darq3KHaU%3Dqikf5yKOQ%40mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml |  27 ++++
 src/backend/utils/adt/encode.c           | 152 ++++++++++++++++++++++-
 src/test/regress/expected/strings.out    | 123 +++++++++++++++++-
 src/test/regress/expected/uuid.out       |  16 +++
 src/test/regress/sql/strings.sql         |  31 ++++-
 src/test/regress/sql/uuid.sql            |   9 ++
 6 files changed, 350 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index b256381e01f..7aa3805f1ec 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -729,6 +729,7 @@
        <parameter>format</parameter> values are:
        <link linkend="encode-format-base64"><literal>base64</literal></link>,
        <link linkend="encode-format-base64url"><literal>base64url</literal></link>,
+       <link linkend="encode-format-base32hex"><literal>base32hex</literal></link>,
        <link linkend="encode-format-escape"><literal>escape</literal></link>,
        <link linkend="encode-format-hex"><literal>hex</literal></link>.
       </para>
@@ -804,6 +805,32 @@
      </listitem>
     </varlistentry>
 
+    <varlistentry id="encode-format-base32hex">
+     <term>base32hex
+      <indexterm>
+       <primary>base32hex format</primary>
+      </indexterm></term>
+     <listitem>
+      <para>
+       The <literal>base32hex</literal> format is that of
+       <ulink url="https://datatracker.ietf.org/doc/html/rfc4648#section-7">
+       RFC 4648 Section 7</ulink>.  It uses the extended hex alphabet
+       (<literal>0</literal>-<literal>9</literal> and
+       <literal>A</literal>-<literal>V</literal>) which preserves sort order
+       when encoding binary data. The <function>encode</function> function
+       produces padded output, while <function>decode</function> accepts both
+       padded and unpadded input. Decoding is case-insensitive and ignores
+       whitespace characters.
+      </para>
+      <para>
+       This format can be used for encoding UUIDs in a compact, sortable format:
+       <literal>substring(encode(uuid_value::bytea, 'base32hex') FROM 1 FOR 26)</literal>
+       produces a 26-character string compared to the standard 36-character
+       UUID representation.
+      </para>
+     </listitem>
+    </varlistentry>
+
     <varlistentry id="encode-format-escape">
      <term>escape
      <indexterm>
diff --git a/src/backend/utils/adt/encode.c b/src/backend/utils/adt/encode.c
index f5f835e944a..793cc7f2a34 100644
--- a/src/backend/utils/adt/encode.c
+++ b/src/backend/utils/adt/encode.c
@@ -65,8 +65,8 @@ binary_encode(PG_FUNCTION_ARGS)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("unrecognized encoding: \"%s\"", namebuf),
-				 errhint("Valid encodings are \"%s\", \"%s\", \"%s\", and \"%s\".",
-						 "base64", "base64url", "escape", "hex")));
+				 errhint("Valid encodings are \"%s\", \"%s\", \"%s\", \"%s\", and \"%s\".",
+						 "base64", "base64url", "base32hex", "escape", "hex")));

 	dataptr = VARDATA_ANY(data);
 	datalen = VARSIZE_ANY_EXHDR(data);
@@ -825,6 +825,144 @@ esc_dec_len(const char *src, size_t srclen)
 	return len;
 }
 
+/*
+ * BASE32HEX
+ */
+
+static const char base32hex_table[] = "0123456789ABCDEFGHIJKLMNOPQRSTUV";
+
+static uint64
+base32hex_enc_len(const char *src, size_t srclen)
+{
+	/* 5 bytes encode to 8 characters, round up to multiple of 8 for padding */
+	return ((uint64) srclen + 4) / 5 * 8;
+}
+
+static uint64
+base32hex_dec_len(const char *src, size_t srclen)
+{
+	/* Decode length is (srclen * 5) / 8, but we may have padding */
+	return ((uint64) srclen * 5) / 8;
+}
+
+static uint64
+base32hex_encode(const char *src, size_t srclen, char *dst)
+{
+	const unsigned char *data = (const unsigned char *) src;
+	uint64		bits_buffer = 0;
+	int			bits_in_buffer = 0;
+	uint64		output_pos = 0;
+	size_t		i;
+
+	for (i = 0; i < srclen; i++)
+	{
+		/* Add 8 bits to the buffer */
+		bits_buffer = (bits_buffer << 8) | data[i];
+		bits_in_buffer += 8;
+
+		/* Extract 5-bit chunks while we have enough bits */
+		while (bits_in_buffer >= 5)
+		{
+			bits_in_buffer -= 5;
+			/* Extract top 5 bits */
+			dst[output_pos++] = base32hex_table[(bits_buffer >> bits_in_buffer) & 0x1F];
+			/* Clear the extracted bits by masking */
+			bits_buffer &= ((1ULL << bits_in_buffer) - 1);
+		}
+	}
+
+	/* Handle remaining bits (if any) */
+	if (bits_in_buffer > 0)
+		dst[output_pos++] = base32hex_table[(bits_buffer << (5 - bits_in_buffer)) & 0x1F];
+
+	/* Add padding to make length a multiple of 8 (per RFC 4648) */
+	while (output_pos % 8 != 0)
+		dst[output_pos++] = '=';
+
+	return output_pos;
+}
+
+static uint64
+base32hex_decode(const char *src, size_t srclen, char *dst)
+{
+	const unsigned char *data = (const unsigned char *) src;
+	uint64		bits_buffer = 0;
+	int			bits_in_buffer = 0;
+	uint64		output_pos = 0;
+	size_t		i;
+	int			pos = 0;		/* position within 8-character group (0-7) */
+	bool		end = false;	/* have we seen padding? */
+
+	for (i = 0; i < srclen; i++)
+	{
+		unsigned char c = data[i];
+		int			val;
+
+		/* Skip whitespace */
+		if (c == ' ' || c == '\t' || c == '\n' || c == '\r')
+			continue;
+
+		if (c == '=')
+		{
+			/*
+			 * The first padding is only valid at positions 2, 4, 5, or 7 within
+			 * an 8-character group (corresponding to 1, 2, 3, or 4 input bytes).
+			 * We only check the position for the first '=' character.
+			 */
+			if (!end)
+			{
+				if (pos != 2 && pos != 4 && pos != 5 && pos != 7)
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+							 errmsg("unexpected \"=\" while decoding base32hex sequence")));
+				end = true;
+			}
+			pos++;
+			continue;
+		}
+
+		/* No data characters allowed after padding */
+		if (end)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("invalid symbol \"%.*s\" found while decoding base32hex sequence",
+							pg_mblen((const char *) &c), (const char *) &c)));
+
+		/* Decode base32hex character (0-9, A-V, case-insensitive) */
+		if (c >= '0' && c <= '9')
+			val = c - '0';
+		else if (c >= 'A' && c <= 'V')
+			val = c - 'A' + 10;
+		else if (c >= 'a' && c <= 'v')
+			val = c - 'a' + 10;
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("invalid symbol \"%.*s\" found while decoding base32hex sequence",
+							pg_mblen((const char *) &c), (const char *) &c)));
+
+		/* Add 5 bits to buffer */
+		bits_buffer = (bits_buffer << 5) | val;
+		bits_in_buffer += 5;
+		pos++;
+
+		/* Extract 8-bit bytes when we have enough bits */
+		while (bits_in_buffer >= 8)
+		{
+			bits_in_buffer -= 8;
+			dst[output_pos++] = (unsigned char) (bits_buffer >> bits_in_buffer);
+			/* Clear the extracted bits */
+			bits_buffer &= ((1ULL << bits_in_buffer) - 1);
+		}
+
+		/* Reset position after each complete 8-character group */
+		if (pos == 8)
+			pos = 0;
+	}
+
+	return output_pos;
+}
+
 /*
  * Common
  */
@@ -854,6 +992,12 @@ static const struct
 			pg_base64url_enc_len, pg_base64url_dec_len, pg_base64url_encode, pg_base64url_decode
 		}
 	},
+	{
+		"base32hex",
+		{
+			base32hex_enc_len, base32hex_dec_len, base32hex_encode, base32hex_decode
+		}
+	},
 	{
 		"escape",
 		{
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index f38688b5c37..12d7da9695d 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2600,14 +2600,131 @@ SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape');
 -- report an error with a hint listing valid encodings when an invalid encoding is specified
 SELECT encode('\x01'::bytea, 'invalid');  -- error
 ERROR:  unrecognized encoding: "invalid"
-HINT:  Valid encodings are "base64", "base64url", "escape", and "hex".
+HINT:  Valid encodings are "base64", "base64url", "base32hex", "escape", and "hex".
 SELECT decode('00', 'invalid');           -- error
 ERROR:  unrecognized encoding: "invalid"
-HINT:  Valid encodings are "base64", "base64url", "escape", and "hex".
+HINT:  Valid encodings are "base64", "base64url", "base32hex", "escape", and "hex".
 --
--- base64url encoding/decoding
+-- base32hex encoding/decoding
 --
 SET bytea_output TO hex;
+SELECT encode('', 'base32hex');  -- ''
+ encode 
+--------
+ 
+(1 row)
+
+SELECT encode('\x11', 'base32hex');  -- '24======'
+  encode  
+----------
+ 24======
+(1 row)
+
+SELECT encode('\x1122', 'base32hex');  -- '24H0===='
+  encode  
+----------
+ 24H0====
+(1 row)
+
+SELECT encode('\x112233', 'base32hex');  -- '24H36==='
+  encode  
+----------
+ 24H36===
+(1 row)
+
+SELECT encode('\x11223344', 'base32hex');  -- '24H36H0='
+  encode  
+----------
+ 24H36H0=
+(1 row)
+
+SELECT encode('\x1122334455', 'base32hex');  -- '24H36H2L'
+  encode  
+----------
+ 24H36H2L
+(1 row)
+
+SELECT encode('\x112233445566', 'base32hex');  -- '24H36H2LCO======'
+      encode      
+------------------
+ 24H36H2LCO======
+(1 row)
+
+SELECT decode('', 'base32hex');  -- ''
+ decode 
+--------
+ \x
+(1 row)
+
+SELECT decode('24======', 'base32hex');  -- \x11
+ decode 
+--------
+ \x11
+(1 row)
+
+SELECT decode('24H0====', 'base32hex');  -- \x1122
+ decode 
+--------
+ \x1122
+(1 row)
+
+SELECT decode('24H36===', 'base32hex');  -- \x112233
+  decode  
+----------
+ \x112233
+(1 row)
+
+SELECT decode('24H36H0=', 'base32hex');  -- \x11223344
+   decode   
+------------
+ \x11223344
+(1 row)
+
+SELECT decode('24H36H2L', 'base32hex');  -- \x1122334455
+    decode    
+--------------
+ \x1122334455
+(1 row)
+
+SELECT decode('24H36H2LCO======', 'base32hex');  -- \x112233445566
+     decode     
+----------------
+ \x112233445566
+(1 row)
+
+SELECT decode('24', 'base32hex');  -- OK, padding `=` are optional
+ decode 
+--------
+ \x11
+(1 row)
+
+SELECT decode('24=======', 'base32hex');  -- OK
+ decode 
+--------
+ \x11
+(1 row)
+
+SELECT decode('24h36h2lco', 'base32hex');  -- OK, the encoding is case-insensitive
+     decode     
+----------------
+ \x112233445566
+(1 row)
+
+SELECT decode('=', 'base32hex');  -- error
+ERROR:  unexpected "=" while decoding base32hex sequence
+SELECT decode('W', 'base32hex');  -- error
+ERROR:  invalid symbol "W" found while decoding base32hex sequence
+SELECT decode('24H36H0=24', 'base32hex'); -- error
+ERROR:  invalid symbol "2" found while decoding base32hex sequence
+SELECT decode('11=', 'base32hex');  -- OK, non-zero padding bits are accepted (consistent with base64)
+ decode 
+--------
+ \x08
+(1 row)
+
+--
+-- base64url encoding/decoding
+--
 -- Simple encoding/decoding
 SELECT encode('\x69b73eff', 'base64url');  -- abc-_w
  encode 
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index d157ef7d0b3..df25a6aa5c5 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -327,5 +327,21 @@ SELECT v = v::bytea::uuid as matched FROM gen_random_uuid() v;
  t
 (1 row)
 
+-- make sure base32hex encoding works with UUIDs and preserves ordering
+SELECT orig, encode(orig :: bytea, 'base32hex') AS enc
+FROM unnest(ARRAY[
+    '123e4567-e89b-12d3-a456-426614174000',
+    '00000000-0000-0000-0000-000000000000',
+    '11111111-1111-1111-1111-111111111111',
+    'ffffffff-ffff-ffff-ffff-ffffffffffff'] :: uuid[]
+) AS orig ORDER BY enc;
+                 orig                 |               enc                
+--------------------------------------+----------------------------------
+ 00000000-0000-0000-0000-000000000000 | 00000000000000000000000000======
+ 11111111-1111-1111-1111-111111111111 | 248H248H248H248H248H248H24======
+ 123e4567-e89b-12d3-a456-426614174000 | 28V4APV8JC9D792M89J185Q000======
+ ffffffff-ffff-ffff-ffff-ffffffffffff | VVVVVVVVVVVVVVVVVVVVVVVVVS======
+(4 rows)
+
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index d8a09737668..28d582f39bd 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -835,10 +835,39 @@ SELECT encode('\x01'::bytea, 'invalid');  -- error
 SELECT decode('00', 'invalid');           -- error
 
 --
--- base64url encoding/decoding
+-- base32hex encoding/decoding
 --
 SET bytea_output TO hex;
 
+SELECT encode('', 'base32hex');  -- ''
+SELECT encode('\x11', 'base32hex');  -- '24======'
+SELECT encode('\x1122', 'base32hex');  -- '24H0===='
+SELECT encode('\x112233', 'base32hex');  -- '24H36==='
+SELECT encode('\x11223344', 'base32hex');  -- '24H36H0='
+SELECT encode('\x1122334455', 'base32hex');  -- '24H36H2L'
+SELECT encode('\x112233445566', 'base32hex');  -- '24H36H2LCO======'
+
+SELECT decode('', 'base32hex');  -- ''
+SELECT decode('24======', 'base32hex');  -- \x11
+SELECT decode('24H0====', 'base32hex');  -- \x1122
+SELECT decode('24H36===', 'base32hex');  -- \x112233
+SELECT decode('24H36H0=', 'base32hex');  -- \x11223344
+SELECT decode('24H36H2L', 'base32hex');  -- \x1122334455
+SELECT decode('24H36H2LCO======', 'base32hex');  -- \x112233445566
+
+SELECT decode('24', 'base32hex');  -- OK, padding `=` are optional
+SELECT decode('24=======', 'base32hex');  -- OK
+SELECT decode('24h36h2lco', 'base32hex');  -- OK, the encoding is case-insensitive
+SELECT decode('=', 'base32hex');  -- error
+SELECT decode('W', 'base32hex');  -- error
+SELECT decode('24H36H0=24', 'base32hex'); -- error
+SELECT decode('11=', 'base32hex');  -- OK, non-zero padding bits are accepted (consistent with base64)
+
+
+--
+-- base64url encoding/decoding
+--
+
 -- Simple encoding/decoding
 SELECT encode('\x69b73eff', 'base64url');  -- abc-_w
 SELECT decode('abc-_w', 'base64url');      -- \x69b73eff
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index f512f4dea1d..278a5773ada 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -152,5 +152,14 @@ SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
 SELECT '\x1234567890abcdef'::bytea::uuid; -- error
 SELECT v = v::bytea::uuid as matched FROM gen_random_uuid() v;
 
+-- make sure base32hex encoding works with UUIDs and preserves ordering
+SELECT orig, encode(orig :: bytea, 'base32hex') AS enc
+FROM unnest(ARRAY[
+    '123e4567-e89b-12d3-a456-426614174000',
+    '00000000-0000-0000-0000-000000000000',
+    '11111111-1111-1111-1111-111111111111',
+    'ffffffff-ffff-ffff-ffff-ffffffffffff'] :: uuid[]
+) AS orig ORDER BY enc;
+
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
-- 
2.43.0

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 18 Mar 2026 10:52:30 -0700

> > I think it's better to have consistent behavior across our encoding.

Thank you for updating the patches!

I've made some minor changes to both patches (e.g., rewording the
documentation changes and commit messages etc), and attached the
updated patches.

I'm going to push these patches unless there is no further comment.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 5fc039df55008ff8578a16cb27697b55407bd6c5 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Wed, 29 Oct 2025 15:53:12 +0400
Subject: [PATCH v7 2/2] Add base32hex support to encode() and decode()
 functions.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds support for base32hex encoding and decoding, as defined in
RFC 4648 Section 7. Unlike standard base32, base32hex uses the
extended hex alphabet (0-9, A-V) which preserves the lexicographical
order of the encoded data.

This is particularly useful for representing UUIDv7 values in a
compact string format while maintaining their time-ordered sort
property.

The encode() function produces output padded with '=', while decode()
accepts both padded and unpadded input. Following the behavior of
other encoding types, decoding is case-insensitive.

Suggested-by: Sergey Prokhorenko <[email-masked]>
Author: Andrey Borodin <[email-masked]>
Co-authored-by: Aleksander Alekseev <[email-masked]>
Reviewed-by: Masahiko Sawada <[email-masked]>
Reviewed-by: Илья Чердаков <[email-masked]>
Discussion: https://postgr.es/m/CAJ7c6TOramr1UTLcyB128LWMqita1Y7%3Darq3KHaU%3Dqikf5yKOQ%40mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml |  27 ++++
 src/backend/utils/adt/encode.c           | 152 ++++++++++++++++++++++-
 src/test/regress/expected/strings.out    | 117 ++++++++++++++++-
 src/test/regress/expected/uuid.out       |  16 +++
 src/test/regress/sql/strings.sql         |  30 ++++-
 src/test/regress/sql/uuid.sql            |   9 ++
 6 files changed, 343 insertions(+), 8 deletions(-)

index b256381e01f..3f77f6d20b0 100644
 
+       <literal>A</literal>-<literal>V</literal>) which preserves the lexicographical
+       sort order of the encoded data. The <function>encode</function> function
+       produces output padded with <literal>'='</literal>, while <function>decode</function>
+       accepts both padded and unpadded input. Decoding is case-insensitive and ignores
+       This format is useful for encoding UUIDs in a compact, sortable format:

 
+/*
+ * BASE32HEX
+ */
+
index f38688b5c37..75a4a4f38a6 100644
@@ -2600,14 +2600,125 @@ SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape');
index d157ef7d0b3..d2a45a0f07c 100644
 
+-- make sure base32hex encoding works with UUIDs and preserves ordering
+SELECT orig, encode(orig::bytea, 'base32hex') AS enc
+FROM unnest(ARRAY[
+    '123e4567-e89b-12d3-a456-426614174000',
+    '00000000-0000-0000-0000-000000000000',
+    '11111111-1111-1111-1111-111111111111',
+    'ffffffff-ffff-ffff-ffff-ffffffffffff']::uuid[]
+) AS orig ORDER BY enc;
+                 orig                 |               enc                
+--------------------------------------+----------------------------------
+ 00000000-0000-0000-0000-000000000000 | 00000000000000000000000000======
+ 11111111-1111-1111-1111-111111111111 | 248H248H248H248H248H248H24======
+ 123e4567-e89b-12d3-a456-426614174000 | 28V4APV8JC9D792M89J185Q000======
+ ffffffff-ffff-ffff-ffff-ffffffffffff | VVVVVVVVVVVVVVVVVVVVVVVVVS======
+(4 rows)
+
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index d8a09737668..9f86f2cac19 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -835,10 +835,38 @@ SELECT encode('\x01'::bytea, 'invalid');  -- error
 SELECT decode('00', 'invalid');           -- error

index f512f4dea1d..ee14802630a 100644
 
+-- make sure base32hex encoding works with UUIDs and preserves ordering
+SELECT orig, encode(orig::bytea, 'base32hex') AS enc
+FROM unnest(ARRAY[
+    '123e4567-e89b-12d3-a456-426614174000',
+    '00000000-0000-0000-0000-000000000000',
+    '11111111-1111-1111-1111-111111111111',
+    'ffffffff-ffff-ffff-ffff-ffffffffffff']::uuid[]
+) AS orig ORDER BY enc;
+
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
-- 
2.53.0

From 113b779dc1c126c4c5d9ebf113b84603051d2d4f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <[email-masked]>
Date: Tue, 28 Oct 2025 16:33:17 +0000
Subject: [PATCH v7 1/2] Allow explicit casting between bytea and uuid.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This enables the use of functions such as encode() and decode() with
UUID values, allowing them to be converted to and from alternative
formats like base64 or hex.

The cast maps the 16-byte internal representation of a UUID directly
to a bytea datum. This is more efficient than going through a text
forepresentation.

Co-authored-by: Aleksander Alekseev <[email-masked]>

index 3017c674040..d8d91678e86 100644
 
+   <para>
+    It is possible to cast <type>uuid</type> values to and from type
+    <type>bytea</type>. This is useful for using functions such as
+    <function>encode()</function> and <function>decode()</function>
+    with UUID values. For example:
+<programlisting>
+encode('1ea3d64c-bc40-4cc3-84bb-6b11ee31e5c2'::uuid::bytea, 'base64')
+decode('HqPWTLxATMOEu2sR7jHlwg==', 'base64')::uuid
+</programlisting>
+   </para>
+
    <para>
     See <xref linkend="functions-uuid"/> for how to generate a UUID in
     <productname>PostgreSQL</productname>.
diff --git a/src/backend/utils/adt/bytea.c b/src/backend/utils/adt/bytea.c
index fd7662d41ee..4dc83671aa5 100644
--- a/src/backend/utils/adt/bytea.c
+++ b/src/backend/utils/adt/bytea.c
@@ -28,6 +28,7 @@
 #include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/sortsupport.h"
+#include "utils/uuid.h"
 #include "varatt.h"
 
 /* GUC variable */

  
 (1 row)
 
+-- casts
 
+-- casts
 
 -- clean up
2.53.0

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 19 Mar 2026 14:18:51 +0300

Hi,

> I'm going to push these patches unless there is no further comment.

Many thanks! One little nitpick.

In 0001:

"""

I'm pretty confident there is no such word "forepresentation".

-- 
Best regards,
Aleksander Alekseev

---

From: Chengxi Sun <[email-masked]>
Date: Thu, 19 Mar 2026 20:12:17 +0800

I have a concern with base32hex_decode(). It only checks where the first =
appears,
but it does not validate the final group length or the required amount of
padding.
Because of that, some invalid inputs are accepted silently.

For example:

postgres=# SET bytea_output = hex;
SET
postgres=# SELECT '0' AS input, decode('0', 'base32hex');
 input | decode
-------+--------
 0     | \x
(1 row)

postgres=# SELECT '000' AS input , decode('000', 'base32hex');
 input | decode
-------+--------
 000   | \x00
(1 row)

postgres=# SELECT '24=' as input , decode('24=', 'base32hex');
 input | decode
-------+--------
 24=   | \x11
(1 row)

These looks good, but if we verify that with python:
% python3 - <<'PY'
import base64

tests = [
    "24",
    "24======",
    "0",
    "000",
    "24=",
]

for s in tests:
    try:
        out = base64.b32hexdecode(s, casefold=True)
        print(f"{s!r} -> OK {out.hex()}")
    except Exception as e:
        print(f"{s!r} -> ERROR: {e}")
PY

The outputs are:
'24' -> ERROR: Incorrect padding
'24======' -> OK 11
'0' -> ERROR: Incorrect padding
'000' -> ERROR: Incorrect padding
'24=' -> ERROR: Incorrect padding

I might be missing some context here, so I wanted to ask: is this behavior
intentional,
or would it make sense to enforce stricter validation for Base32hex input?

Best regards,

Chengxi Sun

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 19 Mar 2026 15:18:48 +0300

Hi,

> I might be missing some context here, so I wanted to ask: is this behavior intentional,

That's intentional - see the discussion above:

"""
[...]

Also see the documentation in respect of padding.

-- 
Best regards,
Aleksander Alekseev

---

From: Chengxi Sun <[email-masked]>
Date: Thu, 19 Mar 2026 22:14:49 +0800

> =# select decode('AB', 'base64');

Thanks for the clarification, that makes sense.

Best regards,

---

From: Dagfinn Ilmari Mannsåker <[email-masked]>
Date: Thu, 19 Mar 2026 14:36:04 +0000

> I'm going to push these patches unless there is no further comment.

Just one minor nitpick on my patch, which is that it should use
palloc_object(), which I wasn't aware of when I wrote it originally.

[...]
> +	uuid = (pg_uuid_t *) palloc(sizeof(pg_uuid_t));

this should be:

+	uuid = palloc_object(pg_uuid_t);

- ilmari

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 19 Mar 2026 12:24:02 -0700

> =# select decode('AB', 'base64');

Right. I've also tested base32hex encoding/decoding with other
libraries such as python's one. IIUC our base32hex implementation
doesn't necessarily work exactly the same as other libraries to have a
better consistency with the existing encodings such as base64 but I
believe that it doesn't contradict the RFC.

Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 19 Mar 2026 14:33:36 -0700

> > +     PG_RETURN_UUID_P(uuid);

Good catch. I've pushed the 0001 patch after incorporating this change.

For 0002 patch, I don't push it yet as I've found a bug in the
decoding code during the self-review:

+           ereport(ERROR,
+                   (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                    errmsg("invalid symbol \"%.*s\" found while
decoding base32hex sequence",
+                           pg_mblen((const char *) &c), (const char *) &c)));

We should not use pg_mblen() anymore (c.f., CVE-2026-2006). And since
'c' is just a single byte on the stack, it leads to a buffer over-read
if the invalid character is a multi-byte character.

Also, a small nitpick is that we can use uint32 instead of uint64 for
'bits_buffer'. I've attached the updated patch as well as the
difference from the previous version.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
diff --git a/src/backend/utils/adt/encode.c b/src/backend/utils/adt/encode.c
index 214d835c624..dc86df27efa 100644
--- a/src/backend/utils/adt/encode.c
+++ b/src/backend/utils/adt/encode.c
@@ -885,17 +885,17 @@ base32hex_encode(const char *src, size_t srclen, char *dst)
 static uint64
 base32hex_decode(const char *src, size_t srclen, char *dst)
 {
-	const unsigned char *data = (const unsigned char *) src;
-	uint64		bits_buffer = 0;
+	const char	*srcend = src + srclen,
+		*s = src;
+	uint32		bits_buffer = 0;
 	int			bits_in_buffer = 0;
 	uint64		output_pos = 0;
-	size_t		i;
 	int			pos = 0;		/* position within 8-character group (0-7) */
 	bool		end = false;	/* have we seen padding? */
 
-	for (i = 0; i < srclen; i++)
+	while (s < srcend)
 	{
-		unsigned char c = data[i];
+		char c = *s++;
 		int			val;
 
 		/* Skip whitespace */
@@ -927,7 +927,7 @@ base32hex_decode(const char *src, size_t srclen, char *dst)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid symbol \"%.*s\" found while decoding base32hex sequence",
-							pg_mblen((const char *) &c), (const char *) &c)));
+							pg_mblen_range(s - 1, srcend), s - 1)));
 
 		/* Decode base32hex character (0-9, A-V, case-insensitive) */
 		if (c >= '0' && c <= '9')
@@ -940,7 +940,7 @@ base32hex_decode(const char *src, size_t srclen, char *dst)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid symbol \"%.*s\" found while decoding base32hex sequence",
-							pg_mblen((const char *) &c), (const char *) &c)));
+							pg_mblen_range(s - 1, srcend), s - 1)));
 
 		/* Add 5 bits to buffer */
 		bits_buffer = (bits_buffer << 5) | val;
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 75a4a4f38a6..0166a57b0d4 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2716,6 +2716,8 @@ SELECT decode('11=', 'base32hex');  -- OK, non-zero padding bits are accepted (c
  \x08
 (1 row)
 
+SELECT decode('あ', 'base32hex'); -- error
+ERROR:  invalid symbol "あ" found while decoding base32hex sequence
 --
 -- base64url encoding/decoding
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 9f86f2cac19..13fcfe21241 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -861,6 +861,7 @@ SELECT decode('=', 'base32hex');  -- error
 SELECT decode('W', 'base32hex');  -- error
 SELECT decode('24H36H0=24', 'base32hex'); -- error
 SELECT decode('11=', 'base32hex');  -- OK, non-zero padding bits are accepted (consistent with base64)
+SELECT decode('あ', 'base32hex'); -- error

 --
From 10b3df363ed5c1ff491e4e006f6b0528fc930ad2 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Wed, 29 Oct 2025 15:53:12 +0400
Subject: [PATCH v8] Add base32hex support to encode() and decode() functions.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Suggested-by: Sergey Prokhorenko <[email-masked]>
Author: Andrey Borodin <[email-masked]>
Co-authored-by: Aleksander Alekseev <[email-masked]>
Reviewed-by: Masahiko Sawada <[email-masked]>
Reviewed-by: Илья Чердаков <[email-masked]>
Discussion: https://postgr.es/m/CAJ7c6TOramr1UTLcyB128LWMqita1Y7%3Darq3KHaU%3Dqikf5yKOQ%40mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml |  27 ++++
 src/backend/utils/adt/encode.c           | 153 ++++++++++++++++++++++-
 src/test/regress/expected/strings.out    | 119 +++++++++++++++++-
 src/test/regress/expected/uuid.out       |  16 +++
 src/test/regress/sql/strings.sql         |  31 ++++-
 src/test/regress/sql/uuid.sql            |   9 ++
 6 files changed, 347 insertions(+), 8 deletions(-)

index f5f835e944a..dc86df27efa 100644

 	dataptr = VARDATA_ANY(data);
 	datalen = VARSIZE_ANY_EXHDR(data);
@@ -825,6 +825,145 @@ esc_dec_len(const char *src, size_t srclen)
 	return len;
 }
 
+/*
+ * BASE32HEX
+ */
+
+	const char	*srcend = src + srclen,
+		*s = src;
+	uint32		bits_buffer = 0;
+	while (s < srcend)
+		char c = *s++;
+			 * The first padding is only valid at positions 2, 4, 5, or 7
+			 * within an 8-character group (corresponding to 1, 2, 3, or 4
+			 * input bytes). We only check the position for the first '='
+			 * character.
+							pg_mblen_range(s - 1, srcend), s - 1)));
+							pg_mblen_range(s - 1, srcend), s - 1)));
@@ -854,6 +993,12 @@ static const struct
index f38688b5c37..0166a57b0d4 100644
@@ -2600,14 +2600,127 @@ SELECT decode(encode('\x1234567890abcdef00', 'escape'), 'escape');
+SELECT decode('あ', 'base32hex'); -- error
+ERROR:  invalid symbol "あ" found while decoding base32hex sequence
 
index d8a09737668..13fcfe21241 100644

+SELECT decode('あ', 'base32hex'); -- error

---

From: Chengxi Sun <[email-masked]>
Date: Fri, 20 Mar 2026 11:06:57 +0800

I've run regression tests, LGTM overall.

Patch v8-0001 had some formatting issues, but they are already addressed in
the fix-issue patch.

Best regards,

Chengxi Sun

---

From: Aleksander Alekseev <[email-masked]>
Date: Fri, 20 Mar 2026 16:02:10 +0300

Hi,

> difference from the previous version.

Then I suggest using uint32 for the bits_buffer variable in
base32hex_encode() too. Also we should use 1U instead of 1ULL with
uint32.

-- 
Best regards,
Aleksander Alekseev
From 02758297caf1022c3f07838b322113238c2eaa90 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Wed, 29 Oct 2025 15:53:12 +0400
Subject: [PATCH v9] Add base32hex support to encode() and decode() functions.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

 
index f5f835e944a..334fa080b95 100644

 
+/*
+ * BASE32HEX
+ */
+
+			bits_buffer &= ((1U << bits_in_buffer) - 1);
+			bits_buffer &= ((1U << bits_in_buffer) - 1);

---

From: Aleksander Alekseev <[email-masked]>
Date: Fri, 20 Mar 2026 17:24:35 +0300

Hi,

> base32hex_encode() too. Also we should use 1U instead of 1ULL with

CI is not happy with the new test:

```
 SELECT decode('あ', 'base32hex'); -- error
-ERROR:  invalid symbol "あ" found while decoding base32hex sequence
+ERROR:  invalid symbol "ã" found while decoding base32hex sequence
```

Although it passes locally. My best guess is that something is off
with the database encoding on CI and that we shouldn't use this test.
We have a similar test which uses ASCII symbols only.

-- 
Best regards,
Aleksander Alekseev
From cf76996ba9370e7c8a0aabf3a322f15ff1353600 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email-masked]>
Date: Wed, 29 Oct 2025 15:53:12 +0400
Subject: [PATCH v10] Add base32hex support to encode() and decode() functions.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

 6 files changed, 344 insertions(+), 8 deletions(-)

 

+/*
+ * BASE32HEX
+ */
+

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 23 Mar 2026 18:17:24 -0700

> We have a similar test which uses ASCII symbols only.

Good catch. Yes, we should not use this test depending on the database
encoding and it seems we can omit this test in the first place.

The patch looks basically good to me. I've made some changes to the
regression test part as I want to have round-trip tests. I've merged
the tests checking the sortability to the existing tests and added
round-trip tests. With this change, we can test round-trip tests and
sortability tests with random UUID value in every test run while
minimizing the test time. Feedback is very welcome.

Regards,

---

From: Aleksander Alekseev <[email-masked]>
Date: Tue, 24 Mar 2026 18:31:41 +0300

Hi,

> > minimizing the test time. Feedback is very welcome.

v11 looks good to me.

> It looks like leading, trailing, and embedded whitespace are all ignored. But I don’t see a test case covering this behavior, so maybe it would be good to add one.

I intentionally didn't include this test because the code is trivial:

``
        /* Skip whitespace */
        if (c == ' ' || c == '\t' || c == '\n' || c == '\r')
            continue;
```

And also because we never tested it for base64. If we want to start
testing it we should add tests both for base64 and base32hex which IMO
should be a separate patch.

-- 
Best regards,
Aleksander Alekseev

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 24 Mar 2026 10:26:34 -0700

Agreed.

I've attached the updated version patch that includes the following points:

- changed the order of encodings in the doc and the hint message to
maintain alphabetical order.
- changed the query example to extract data from the encoded UUID
value to use rtrim() as it's more intuitive.
- added some regression tests for decoding unpadded inputs.

I'm going to push the patch unless there are comments on these changes.

Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 25 Mar 2026 18:09:22 -0700

On Wed, Mar 25, 2026 at 5:35 PM Tom Lane <[email-masked]> wrote:
>
> Tomas Vondra <[email-masked]> writes:
> > On 3/26/26 00:40, Tom Lane wrote:
> >> I believe what's happening there is that in cs_CZ locale,
> >> "V" doesn't follow simple ASCII sort ordering.
>
> > With cs_CZ all letters sort *before* numbers, while in en_US it's the
> > other way around. V is not special in any way.
>
> Ah, sorry, I should have researched a bit instead of relying on
> fading memory.  The quirk I was thinking of is that in cs_CZ,
> "ch" sorts after "h":
>
> u8=# select 'h' < 'ch'::text collate "en_US";
>  ?column?
> ----------
>  f
> (1 row)
>
> u8=# select 'h' < 'ch'::text collate "cs_CZ";
>  ?column?
> ----------
>  t
> (1 row)
>
> Regular hex encoding isn't bitten by that because it doesn't
> use 'h' in the text form ... but this base32hex thingie does.
>
> However, your point is also correct:
>
> u8=# select '0' < 'C'::text ;
>  ?column?
> ----------
>  t
> (1 row)
>
> u8=# select '0' < 'C'::text collate "cs_CZ";
>  ?column?
> ----------
>  f
> (1 row)
>
> and that breaks "text ordering matches numeric ordering"
> for both traditional hex and base32hex.  So maybe this
> is not as big a deal as I first thought.  We need a fix
> for the new test though.  Probably adding COLLATE "C"
> would be enough.

Thank you for the report and the analysis.

I've reproduced the issue with "cs_CZ" collation and adding COLLATE
"C" to the query resolves it. It seems also a good idea to add a note
in the documentation too as users might face the same issue. For
example,

To maintain the lexicographical sort order of the encoded data, ensure
that the text is sorted using the C collation (e.g., using COLLATE
"C"). Natural language collations may sort characters differently and
break the ordering.

Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 25 Mar 2026 18:21:38 -0700

> "C"). Natural language collations may sort characters differently and

Attached the patch doing the above idea.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From a64f3f64a9f04c1f5da9a51fe760c40480585fd4 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email-masked]>
Date: Wed, 25 Mar 2026 18:07:11 -0700
Subject: [PATCH] Fix UUID sortability tests in base32hex encoding.

The recently added test for base32hex encoding of UUIDs failed on
buildfarm member hippopotamus using natural language locales (such as
cs_CZ). This happened because those collations may sort characters
differently, which breaks the strict byte-wise lexicographical
ordering expected by base32hex encoding.

This commit fixes the regression tests by explicitly using the C
collation. Additionally, add a note to the documentation to warm users
that they must use the C collation if they want to maintain the
lexicographical sort order of the encoded data.

Per buildfarm member hippopotamus.

Analyzed-by: Tom Lane <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/func/func-binarystring.sgml | 9 +++++++++
 src/test/regress/expected/uuid.out       | 7 +++++--
 src/test/regress/sql/uuid.sql            | 7 +++++--
 3 files changed, 19 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index 0aaf9bc68f1..2ad2cdbea82 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -790,6 +790,15 @@
        produces a 26-character string compared to the standard 36-character
        UUID representation.
       </para>
+
+      <note>
+       <para>
+        To maintain the lexicographical sort order of the encoded data,
+        ensure that the text is sorted using the C collation
+        (e.g., using <literal>COLLATE "C"</literal>). Natural language
+        collations may sort characters differently and break the ordering.
+       </para>
+      </note>
      </listitem>
     </varlistentry>
 
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 142c529e693..9c5dda9e9ab 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -236,8 +236,11 @@ SELECT array_agg(id ORDER BY guid_field) FROM guid3;
  {1,2,3,4,5,6,7,8,9,10,11,12}
 (1 row)
 
--- make sure base32hex encoding works with UUIDs and preserves ordering
-SELECT array_agg(id ORDER BY guid_encoded) FROM guid3;
+-- Test base32hex encoding of UUIDs and its lexicographical sorting property.
+-- COLLATE "C" is required to prevent buildfarm failures in non-C locales
+-- where natural language collations (such as cs_CZ) would break strict
+-- byte-wise ordering.
+SELECT array_agg(id ORDER BY guid_encoded COLLATE "C") FROM guid3;
           array_agg           
 ------------------------------
  {1,2,3,4,5,6,7,8,9,10,11,12}
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index f2ff00f5ddd..8cc2ad40614 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -122,8 +122,11 @@ INSERT INTO guid3 (guid_field) SELECT uuidv7() FROM generate_series(1, 10);
 INSERT INTO guid3 (guid_field) VALUES ('ffffffff-ffff-ffff-ffff-ffffffffffff'::uuid);
 SELECT array_agg(id ORDER BY guid_field) FROM guid3;
 
--- make sure base32hex encoding works with UUIDs and preserves ordering
-SELECT array_agg(id ORDER BY guid_encoded) FROM guid3;
+-- Test base32hex encoding of UUIDs and its lexicographical sorting property.
+-- COLLATE "C" is required to prevent buildfarm failures in non-C locales
+-- where natural language collations (such as cs_CZ) would break strict
+-- byte-wise ordering.
+SELECT array_agg(id ORDER BY guid_encoded COLLATE "C") FROM guid3;
 
 -- Check the timestamp offsets for v7.
 --
-- 
2.53.0

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 25 Mar 2026 20:13:55 -0700

> Attached the patch doing the above idea.

Pushed the fix without the documentation changes to make the buildfarm
animals happy first.

Regards,

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 26 Mar 2026 21:37:18 +0500

> On 26 Mar 2026, at 04:40, Tom Lane <[email-masked]> wrote:
> 
> I wonder whether this discovery puts enough of a hole in the
> value-proposition for base32hex that we should just revert
> this patch altogether.  

After thinking more about it, I do not see grounds for reverting.

> "It works except in some locales"

It works per RFC. It adds value. It's documented precisely.

Sortability in cs_CZ only stands in a way for this format to become "the one UUID format to rule them all" instead of canonical in the future.
We should let IETF WG know that digits and letters are not always ordered as they expect. Hopefully Sergey will handle this.

BTW, thanks to Alexander and Masahiko for pushing this to finish line! I'm listed as author, but they done 99.9% of work on making this functionality.

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 26 Mar 2026 10:30:21 -0700

On Thu, Mar 26, 2026 at 1:32 AM Andrey Borodin <[email-masked]> wrote:
> > this patch altogether.  "It works except in some locales"
> > isn't a very appetizing prospect, so the whole idea is starting
> > to feel more like a foot-gun than a widely-useful feature.
>
> To be precise, this discovery cast shadows on argument "[base32hex is ]lexicographically sortable format that preserves temporal ordering for UUIDv7". And, actually, any UUID. But I do not think it invalidates the argument completely.
>
> It's taken from RFC[0], actually, that states:
>  One property with this alphabet, which the base64 and base32
>  alphabets lack, is that encoded data maintains its sort order when
>  the encoded data is compared bit-wise.
>
>
> RFC does not give any other benefits.
> Personally, I like that it's compact, visually better than base64, and RFC-compliant.
> And IMO argument "base32hex is lexicographically sortable format that preserves ordering for UUID in C locale" is still very strong.
> Though, there's a little footy shooty in last 3 words.

Yeah, I still find that base32hex is useful.

As I mentioned in another email, I think we should make a note the
fact that "base32hex is lexicographically sortable format that
preserves ordering for UUID in C locale" in the documentation. I've
attached the patch. Feedback is very welcome.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 515c666b60f7f81f6b2a004ebfb91b358188470c Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email-masked]>
Date: Thu, 26 Mar 2026 10:17:23 -0700
Subject: [PATCH v1] doc: Add note about collation requirements for base32hex
 sortability.

While fixing the base32hex UUID sortability test in commit 89210037a0a,
it turned out that the expected lexicographical order is only maintained
under the C collation (or an equivalent byte-wise collation).

Since this is not just a testing quirk but could be a real trap users
might fall into when sorting encoded data in their databases, we added
a note to the documentation to make this requirement explicitly clear.

Reviewed-by:
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/func/func-binarystring.sgml | 8 ++++++++
 1 file changed, 8 insertions(+)

index 0aaf9bc68f1..9f731d7bca0 100644
@@ -790,6 +790,14 @@
 
-- 
2.53.0

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 26 Mar 2026 22:59:30 +0500

> On 26 Mar 2026, at 22:30, Masahiko Sawada <[email-masked]> wrote:
> 
>  Feedback is very welcome.

The patch is fine from my POV.

Please consider these small improvements to the patch. Basically, we reference to formula stated by RFC where possible.
0001 is intact.

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 26 Mar 2026 15:01:04 -0700

> Please consider these small improvements to the patch. Basically, we reference to formula stated by RFC where possible.

Thank you for the suggestion. It looks good to me.

I've merged these patches and am going to push barring any objections.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 35d321a9e3216052c917b4d1a61b93ecb1414e42 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email-masked]>
Date: Thu, 26 Mar 2026 10:17:23 -0700
Subject: [PATCH v2] doc: Clarify collation requirements for base32hex
 sortability.

While fixing the base32hex UUID sortability test in commit
89210037a0a, it turned out that the expected lexicographical order is
only maintained under the C collation (or an equivalent byte-wise
collation). Natural language collations may employ different rules,
breaking the sortability.

This commit updates the documentation to explicitly state that
base32hex is "byte-wise sortable", ensuring users do not fall into the
trap of using natural language collations when querying their encoded
data.

Co-Authored-by: Masahiko Sawada <[email-masked]>
Co-Authored-by: Andrey Borodin <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/func/func-binarystring.sgml | 14 +++++++++++---
 1 file changed, 11 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index 0aaf9bc68f1..dc6b7e57ea7 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -778,18 +778,26 @@
        <ulink url="https://datatracker.ietf.org/doc/html/rfc4648#section-7">
        RFC 4648 Section 7</ulink>.  It uses the extended hex alphabet
        (<literal>0</literal>-<literal>9</literal> and
-       <literal>A</literal>-<literal>V</literal>) which preserves the lexicographical
-       sort order of the encoded data. The <function>encode</function> function
+       <literal>A</literal>-<literal>V</literal>) which preserves the sort order of
+       the encoded data when compared byte-wise. The <function>encode</function> function
        produces output padded with <literal>'='</literal>, while <function>decode</function>
        accepts both padded and unpadded input. Decoding is case-insensitive and ignores
        whitespace characters.
       </para>
       <para>
-       This format is useful for encoding UUIDs in a compact, sortable format:
+       This format is useful for encoding UUIDs in a compact, byte-wise sortable format:
        <literal>rtrim(encode(uuid_value::bytea, 'base32hex'), '=')</literal>
        produces a 26-character string compared to the standard 36-character
        UUID representation.
       </para>
+      <note>
+       <para>
+        To maintain the lexicographical sort order of the encoded data,
+        ensure that the text is sorted using the C collation
+        (e.g., using <literal>COLLATE "C"</literal>). Natural language
+        collations may sort characters differently and break the ordering.
+       </para>
+      </note>
      </listitem>
     </varlistentry>
 
-- 
2.53.0

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 27 Mar 2026 12:16:07 -0700

> I've merged these patches and am going to push barring any objections.

Pushed.

As for the original base32hex encoding commit, I'd like to leave a
note that I've changed the patch to use the lookup table for decoding
before the push as it's more efficient.

Regards,