Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCQUx-00DvfY-UM for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Oct 2025 22:43:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vCQUw-001Kcn-Py for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Oct 2025 22:43:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCQUv-001Kc0-C6 for pgsql-hackers@lists.postgresql.org; Fri, 24 Oct 2025 22:43:01 +0000 Received: from sonic311-24.consmr.mail.gq1.yahoo.com ([98.137.65.205]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vCQUr-003btX-0o for pgsql-hackers@postgresql.org; Fri, 24 Oct 2025 22:42:59 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.au; s=s2048; t=1761345774; bh=n5r/hBuqjluOuEGDvSZbDlnWN6gqXmWQDs3yOq3GVK8=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=VBt7Py/o5LHFHDD66vi+92VvAhjwk0lydL787XrWM5l5bADy3t6cIlU35ld/KrnCGm4Itjh93mIEJehKCm1x6zrW1DPTQJj5CQyvZMAvzJxqxey020azcaW68mR3MyErIniU5g1alb1hdGaFtWFh8yIlBqGHAMLPJRZon1cqSPtAlIIHyRwISgnJqbRiEWNL850x3oPe8bN79b/juibJSIqikxsPzj5e6pJq2Be5JJdYc6R9AxFKqRCIdc6vPrqGU2ttPpiZIjTguA17TmrgeQtfmRUBgV7e9O1fITwIcQvGYkXg9JA/D6FLeBCDyhDqJ5jjklYNbamgq53W1AH8Pg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1761345774; bh=1iBJXDefbijzlD1+V+0InZ/PYi2IW6C1vWFSuvG/2q4=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=QX4HBxYSgS+5zb1OxgDke3fNeT6uOCZsoFUvNut8v50tkPwhtWP9MAzOOx+TT0LpxoDh/Q2IG0iSj8Wyl4qncv7dfjSbfCoak0L0vDTOysc0ZCrFuQfnFHKl8JIwO9MiphuUWWAvrZ1aLvy9WarEX4xgT9dSm1ON3kCzVtotIrmNBGhxMT2FLqpLrYfYzz8g9IYXS7dmzi+B9L3eR4iJlPviD8TZNv2dh5mWUlgUHxzjowchluSzMyLb1esZl+IWVFSV/NzRwi1T6CwOQbeugW99Bta6SqwGH8T6iRQzunlHGWhiaBdqmvTHzpKp0A/BYeusjcFDof1cJZ1HNVUZUQ== X-YMail-OSG: O9mZ_Z0VM1kfTiS1m.VpKP3f6ZCR0q7VNLGiH4PMsXWT1k_.0affT8VqqK9elWx AryMkyNntZuombQ73Wf8rzpj7AVzb7AREI5SF3n253F5.VBRRw3g2ShXKgeDqaSfx7Y_7XZKSx1I fPL8gUEK.rFltrrbf5y57AJNJ6C5eNa.1MIGnYHkdBi.nCK1sFH4rPcWwar.tYfFKZLWhukk_fRh H.WbF5m.WvSJftRoHeSMK44x_ENGs9CXABSzaq6mneasSXQKujKi09TicqWFANebtL22_RUvWEFm 60PKl_jLUxUHYJe8d3HiZXKWT9bwoF6eunv_x268N4XuYuX9WCb.JxxergUn.IF55eRgFbtNxIsx 9vi0T3VsKaGvvzIGeryAf3hNcB0VboGPk45J7Lpt_.vOneE0G8NXTqP9ZwQiaq72lJYfR4iDDDJq MK5kuorWmrjWEYdpgMRMTRmgRBgP7zrrsGDLeyVOErHv3UcEo18KgF.MkR5BOnyqEH7WtLngyb85 BhRaHnRCk6dTxjTTTZOInAHbAyyucI.CFIPvdODEpGxBXjQV2C3lWganpP21U8bUE1Ex.bkqh6pl Skw9PZRQoP1zFNNhMAzZ3SPIG2kJYERWf1uEBUjpb5LD7Vpc1tG1CVsqM4P633soJ4ZNHysXXEHQ hVdqlLaoW2QvgfjJyU.KskB6rhNkhTWW.lrzAse7OosGfldEGclmPpFTnl86obmyhCqi4pTvR0In kaRoTKTfL7278pqqXXKLKMriFxGgv8OzsjNzFQ9o04QHTEwCffURW8InlPpk2lewO.PV3qU.2Hh2 4wvoFK7.eAQLrI4OqOiRQd_cKGafXaNEk6a4ONjzJ96B1RuqLlCOLmpgQVjFSCV4XOHYq.JMa700 JZI3cx2MCM2fRNZJVpiYmQ1QQVRH0vb9Kb7DNhDSysAR9QAxLmNVdkMAV7qYvIuQWqLfL08gqRsq VNNm93zzSVuX53djI4RzVfvoPD4_qR7HXUse7ODm46j9GDdmqwR6sZSneqmxVkMifIs05JsNDw0Q pgG24PYH7cb7XoL1vhNAoUc1WSDUYZ_vxs.9x7KZUmjaN_1nr9VhivDvDTcuW8X4bdlE_j93RakG cNYO2Am6UqGG.ns8DlkW_YdAvv5xTNjRXyguHDhEYeXAWl536OGCIUFDHvbBBeXTmDz1zocsTK20 qCJpxh.770lUe0dOy8BW8XSmoQefGl6x2fCWuM9DhVtIeZY6RfPXTxDl41IQuwxo_RslFPVuJQgR 4UhM9NB0rH811KRko8LwM3MQ7OY5RinIDZQWGRHOe9TrSVX_v0_IqeQo12a3o.TCMqGkyKHh7Yjh k52NDZ0FZ.w4ILkBLopadgK2IhbpYRjQcmv4tiI0fILI2xM2vbOESzaeUjAcN93Xg2kcsRguQoUG cJ9mY1Kt36X1hBlvuddmO6MhcwD4oXakr5HeZxg41qdeJ0LnK.EqalE.o.claJsgy7bR8DIJk_VI GJ.nYV1fnCwtMR1a7PBeHa5.lRY5OpbLPh60qWP2nhCuly_t1yCWb2oZvTVUBpHF1ytHwSlyoI9x ohZF7FN7EFAOon68bI.aq8oEcLDtbItGEdaXC5gdrJEclrm1sfk_DjTD26769yatPMPZR3xetMhI .G8xv5ci9hRTTgXWkVpmRYpOUGbqsoPTIpml9Aqwe_7ewaVOHMt3NP7LjHkyhvdK_KuuO7Lj8P6W AzeGTYzOXFP9K7J5ADjd1PHFAJsBrlz4MGP.XXjMLsVl6yNSh3bkGEw8G4UIRzmRNMNL0Ij6i94_ .AUcApzk2jvipnhzaKZtwat.ketpFV0TNobjgRaMTk3YShrtSpaUNhkVN12dNQ01fBa4UGOhbMUQ 8NXOpTGGUua5Z.QQBakl2rDjqzJNiMz4BgBasMiZAfgGIye1SNQTJItRjXAExldhjMDySzv4R9EZ NNzWJM6LryvULLIQ6ppcVm0Bqkw3.Jt5SvtB6JG5EuOkmiLtfYlNoRuYplzP48ZZt4UqdRcRD7yN id8b1ZsGbldT2WOTp8ARyUP1lJ9L.QRbYvrpw3bbQ00KJui6jjwP9d1eZsFDsvjO0IG6.5rPz2xR DOO_8YVfdUh.0SJI68JClPldRHhzFeyyzjb3C0U7GJHyoxgFSPkhcHwKLJyhHi0qlgqGy1I6nnlI gk04AK8vqyqhehwu_ZWzq3u9rKXP0iqIFrH_MdQXsgYmAswR78cGcEPzNcuwV0Qzpr.g8hLycAm6 UREdCJUqgcvGESGASn1Kdu90hTlzRJP265ncVCQaHmQ2QTJayuWN4qJK1QwHsa06NoJxwinn9K6R 4W8_qW9Zg2RN40wsuYRnFn6HcT7bp8tvDZIQcuiKn_XQqpcu.tP.a9fcfKSKNibZpR0U- X-Sonic-MF: X-Sonic-ID: a4766424-af3f-4741-af44-d22f5dd3fc80 Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.gq1.yahoo.com with HTTP; Fri, 24 Oct 2025 22:42:54 +0000 Date: Fri, 24 Oct 2025 22:39:45 +0000 (UTC) From: Sergey Prokhorenko To: Masahiko Sawada Cc: Andrey Borodin , pgsql-hackers Message-ID: <953203149.383019.1761345585325@mail.yahoo.com> In-Reply-To: References: <1791665551.452444.1761209220211.ref@mail.yahoo.com> <1791665551.452444.1761209220211@mail.yahoo.com> <18022523-0F8F-4C07-AFF5-57DC9086D78E@yandex-team.ru> <1895971769.8343.1761240853939@mail.yahoo.com> <574624399.175025.1761290201491@mail.yahoo.com> Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_383018_629584957.1761345585322" X-Mailer: WebService/1.1.24652 YMailNorrin Content-Length: 11258 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_383018_629584957.1761345585322 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Friday 24 October 2025 at 09:24:15 pm GMT+3, Masahiko Sawada wrote: =20 =20 On Fri, Oct 24, 2025 at 12:17=E2=80=AFAM Sergey Prokhorenko wrote: > > > 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 c= onvention. 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 no= t 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') -> 06= AJBM9TUTSVND36VA87V8BVJO >=C2=A0Does it mean the first argument is uuid type data and when >=C2=A0'uuid_to_base32hex' is specified as the format the function requires= a >=C2=A0uuid data at the first argument? Yes, that's right.PostgreSQL will automatically cast the string '019535d9-3= df7-79fb-b466-fa907fa17f9e'=C2=A0to the uuid type, since the format is corr= ect. >=C2=A0I could not understand the difference >=C2=A0between specifying 'based32hex' and 'uuid_to_base32hex' when encodin= g >=C2=A0UUID data with base32hex encoding. 1. Specifying 'based32hex' in=C2=A0encode function means the first paramete= r is of bytea type as usual.=20 But=C2=A0specifying=C2=A0'uuid_to_base32hex'=C2=A0means the first parameter= is of uuid=C2=A0type. 2.=C2=A0The encode function does not yet support format based32hex.=C2=A0Th= erefore, it is not known whether padding =3D=3D=3D=3D=3D should be used. But=C2=A0padding =3D=3D=3D=3D=3D is not used when=C2=A0specifying 'uuid_to_= base32hex'. > decode('06AJBM9TUTSVND36VA87V8BVJO', 'base32hex_to_uuid') -> 019535d9-3df= 7-79fb-b466-fa907fa17f9e >=C2=A0Suppose that the decode() takes text data at the first argument and >=C2=A0returns UUID data, the function signature would be decode(text, text= ) >=C2=A0-> uuid. But we cannot create two functions that have the same name >=C2=A0and 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 convers= ion with encode/decode functions, which only apply to bytea. UUID and bytea= are fundamentally different data types.=C2=A0Meanwhile, PostgreSQL has ove= r 30 other type conversion functions that deal with other data types. For e= xample, array_to_string, string_to_array, jsonb_to_record, to_char, to_time= stamp, and to_hex. In this situation, the best solution would be to revert = to the original uuid_to_base32hex() and base32hex_to_uuid() functions rathe= r than deal with type incompatibility issues. >=C2=A0Regards, >=C2=A0--=20 >=C2=A0Masahiko Sawada >=C2=A0Amazon Web Services: https://aws.amazon.com =20 ------=_Part_383018_629584957.1761345585322 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


=20
=20
On Friday 24 October 2025 at 09:24:15 pm GMT+3, Mas= ahiko Sawada <sawada.mshk@gmail.com> wrote:


=20 =20
On Fri, Oct 24,= 2025 at 12:17=E2=80=AFAM Sergey Prokhorenko
<sergeyprokhorenko@yahoo.com.au> wrote:
>
>
> Masahiko,
>
> Developers will still be able to use = the long canonical 'hex' UUID format for compatibility. But the short forma= t 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 b= ase36, and 25% even use erroneously sorted base64. That's a very real night= mare. You, too, have every reason not to want to increase the number of bui= lt-in functions in PostgreSQL.
>
>= ; But here is a solution that I hope will satisfy everyone:
>
> encode('019535d9-3df7-79fb-b466-fa907fa17f9e= ', 'uuid_to_base32hex') -> 06AJBM9TUTSVND36VA87V8BVJO
=
> Do= es it mean the first argument is uuid type data and when
= > 'uuid_to_base32hex' i= s specified as the format the function requires a
<= span style=3D"color: rgb(38, 40, 42); font-family: Helvetica Neue, Helvetic= a, Arial, sans-serif;">> uuid data at the first argum= ent?

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 und= erstand the difference
>=  between specifying 'based32hex' and 'uuid_to_base32hex'= when encoding
> UUID data with base32hex encoding.


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


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


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


> d= ecode('06AJBM9TUTSVND36VA87V8BVJO', 'base32hex_to_uuid') -> 019535d9-3df= 7-79fb-b466-fa907fa17f9e

> Suppose that the decode() takes te= xt data at the first argument and
> returns UUID data, the function signature wo= uld be decode(text, text)
&= gt; -> uuid. But we cannot create two functions that = have the same name
>&nbs= p;and the same argument types.
Yes, you're right. This is a problem that can't be solved without co= mposite return values. We clearly took the wrong approach by couplin= g UUID conversion with encode/decode functions, which only apply to = bytea. UUID and bytea are fundamentally different data types.&= nbsp;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 b= ase32hex_to_uuid() functions rather than deal with type incompatibility iss= ues.


> Regards,

> --
> Masahiko Sawada> Amaz= on Web Services: https://aws.amazon.com

------=_Part_383018_629584957.1761345585322--