MIME-Version: 1.0
References: <1791665551.452444.1761209220211.ref@mail.yahoo.com>
 <1791665551.452444.1761209220211@mail.yahoo.com>
 <CAJ7c6TOramr1UTLcyB128LWMqita1Y7=arq3KHaU=qikf5yKOQ@mail.gmail.com>
 <18022523-0F8F-4C07-AFF5-57DC9086D78E@yandex-team.ru>
 <1895971769.8343.1761240853939@mail.yahoo.com>
 <CAD21AoCxy_3VW2_z5Rxc-tFsuqeyGsA-F_kD-tx6XXBC56nTCg@mail.gmail.com>
 <574624399.175025.1761290201491@mail.yahoo.com>
 <CAD21AoAXQcZ2mMkxX6NPdFpdC-D3AhE--qyH9Se3XTrDX6x-bg@mail.gmail.com>
 <953203149.383019.1761345585325@mail.yahoo.com>
 <CAD21AoCzEDdwpyPwA0d-QmCRe5rMz3m160SJgxMwKke85e8n0w@mail.gmail.com>
 <6F76FA61-E2DC-44EF-9504-889D9BDB4EBD@yandex-team.ru>
 <CAD21AoCKvYCccndY4CRcwk1bOuWxJionOidEtKQWoJTqS7wL1g@mail.gmail.com>
 <1154454839.957923.1761604611424@mail.yahoo.com>
In-Reply-To: <1154454839.957923.1761604611424@mail.yahoo.com>
From: Jelte Fennema-Nio <postgres@jeltef.nl>
Date: Tue, 28 Oct 2025 10:30:04 +0100
Message-ID: 
 <CAGECzQTb9pg2Qw0XmOEKaJivLJ8kdGq3Cq38OqSgwiFVD=9f8A@mail.gmail.com>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in
 functions
To: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>
Cc: Andrey Borodin <x4mmm@yandex-team.ru>,
 Masahiko Sawada <sawada.mshk@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAGECzQTb9pg2Qw0XmOEKaJivLJ8kdGq3Cq38OqSgwiFVD%3D9f8A%40mail.gmail.com>
Precedence: bulk

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

On Mon, 27 Oct 2025 at 23:37, Sergey Prokhorenko
<sergeyprokhorenko@yahoo.com.au> wrote:
> I wanted to highlight an important discussion among the authors and contr=
ibutors of RFC 9562 regarding UUID text encoding:
>
> https://github.com/uuid6/new-uuid-encoding-techniques-ietf-draft/discussi=
ons/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 fo=
rmat 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 f=
irst-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 throug=
h 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 exi=
sted for decades without base32hex encoding, and that's worked fine, becaus=
e bytea represents arbitrary binary data, not universally unique identifier=
s with specific structural properties and needs.

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.