public inbox for [email protected]
help / color / mirror / Atom feedFrom: Masahiko Sawada <[email protected]>
To: Andrey Borodin <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Aleksander Alekseev <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Chao Li <[email protected]>
Cc: Dagfinn Ilmari Mannsåker <[email protected]>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
Date: Thu, 26 Mar 2026 15:01:04 -0700
Message-ID: <CAD21AoDyax67fwjmO3MWh_m5P_5u-x2cEx36M=AJuov_fOTNdw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAD21AoCxy_3VW2_z5Rxc-tFsuqeyGsA-F_kD-tx6XXBC56nTCg@mail.gmail.com>
<[email protected]>
<CAD21AoAXQcZ2mMkxX6NPdFpdC-D3AhE--qyH9Se3XTrDX6x-bg@mail.gmail.com>
<[email protected]>
<CAD21AoCzEDdwpyPwA0d-QmCRe5rMz3m160SJgxMwKke85e8n0w@mail.gmail.com>
<[email protected]>
<CAD21AoCKvYCccndY4CRcwk1bOuWxJionOidEtKQWoJTqS7wL1g@mail.gmail.com>
<[email protected]>
<CAGECzQTb9pg2Qw0XmOEKaJivLJ8kdGq3Cq38OqSgwiFVD=9f8A@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAD21AoCf-0LdE=Js3ViXmKfSATJOjSNym1+80O+=-Y3X6LDMyA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAJ7c6TNCGT2eZ8JttAZKUhVv3jJFTHvjFw6typzSGkM5_vePow@mail.gmail.com>
<CAD21AoCcikUgU5coyvM7PLJg5M-75LzQhm-Uxmqn_7M=DHkj+w@mail.gmail.com>
<CAJ7c6TPu7NGrSj-V2-ns0GTNGx9cmoKQgzt-p2MpJepiGjQnYw@mail.gmail.com>
<CAD21AoC2Ooy=W=4YjQ_GO5nEYZ2tsn43=TkLf7ysE-96ygohKg@mail.gmail.com>
<[email protected]>
<CAD21AoA7UMWG8YO2Edf1UWSdtaof6gOnF98bJGoz1z1JBzozuQ@mail.gmail.com>
<CAJ7c6TMd4ebu7oha2C9QZ_aKNE5-keEJ=bE_k_-vrMuw2p1LNg@mail.gmail.com>
<CAJ7c6TNzQES3EhzoftNUEg=mfi69SYEQ=Fh-=rr84b7zHECHeg@mail.gmail.com>
<CAD21AoAj+KOc74wOr04oGo9ySYmf_TYkMi5fByLF80cqyZmB=g@mail.gmail.com>
<[email protected]>
<CAJ7c6TN7fjWDWSVmhQuNCPYLVL9cs9ifc4D_Hj6wUQd2Lw2oNQ@mail.gmail.com>
<CAD21AoAMST1V1OE=yvg3vQn=p6DDJBAaaFDx39z=P-+Z9iXwOQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAD21AoB2Qe0VYWw_84WH6Zuip1DMNNhLpUu5b+r77CUS3yfmZg@mail.gmail.com>
<[email protected]>
On Thu, Mar 26, 2026 at 10:59 AM Andrey Borodin <[email protected]> wrote:
>
>
>
> > On 26 Mar 2026, at 22:30, Masahiko Sawada <[email protected]> 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.
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
Attachments:
[text/x-patch] v2-0001-doc-Clarify-collation-requirements-for-base32hex-.patch (2.8K, 2-v2-0001-doc-Clarify-collation-requirements-for-base32hex-.patch)
download | inline diff:
From 35d321a9e3216052c917b4d1a61b93ecb1414e42 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
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 protected]>
Co-Authored-by: Andrey Borodin <[email protected]>
Discussion: https://postgr.es/m/CAD21AoAwX1D6baSGuQXm0mzPXPWB07kgaoaaahjNHHenbdY24A@mail.gmail.com
---
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
view thread (63+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
In-Reply-To: <CAD21AoDyax67fwjmO3MWh_m5P_5u-x2cEx36M=AJuov_fOTNdw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox