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 1vBxta-007NQg-FB for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Oct 2025 16:10:34 +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 1vBxtZ-00A8PP-0b for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Oct 2025 16:10:32 +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 1vBuWu-008Zfq-1p for pgsql-hackers@lists.postgresql.org; Thu, 23 Oct 2025 12:34:55 +0000 Received: from sonic301-21.consmr.mail.gq1.yahoo.com ([98.137.64.147]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vBuWq-003LgN-1o for pgsql-hackers@postgresql.org; Thu, 23 Oct 2025 12:34:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.au; s=s2048; t=1761222889; bh=gqtJSot0WglSsLVBqB0Uzz6eCigBYS0uf/s8ctPwGfA=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=Kr2grw6WO9M5XXi11CdlZ0ZOKtaDk756p06qhvjuoVST/EImk7tcpM9hmpmGR+fnRYGO7oF/5oEaZsIKu1JTRPmXerpZLRUOQxNPY4LEafkO0KnBTGdXhYpzFbsaBRhVZnegC1pxijQ2Ypo64EWT5QH5H7IwM1ZMJFWkuOZX3rYXb8horq2ulb0q69TwGtlRdJxzpIhkGd7NdhHzahoJvYEtzu2LookXmWh0NLyRn521Ox5toA3E7Dk+RhsIP1/IV4BhKMCZaqunQV5QOjUlKu4Z0zrfvEX0NmJilYL6+gLNbu4Uiz+aFevvRjByBa4LrufGfOYcf0TAyJgiO1OEVw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1761222889; bh=f6aWBYfOC7OIghBlk8u1sZL0GnEPj+1KZWS4Q9Ctdmo=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=LHZlEKP4u7kF3VoUWT7OL6d0bnvpJJKM1KI9vX3/ccN49uq5TQcXN+Fio+lBwnStNtY4II/QAmkOaRMzc5G/M8OZWncswx8oyG8PihUS6WwUyghmeTHFNiESwP+k3yozw0LH98n/QBiP+S+IyzbGsgDrHmqdJBw8bM03ORE28NvGl+4UcHghdJXkkWJdubVh1qDAIxY09ml1wKQfAlsIK2ryJmokBBOYXgmayzqgM/+B/VNRAS5uJ+ox7i75L3fy0ufm/AG3D3DAXFDDIzikg3CXUyzF/j4/LOsFe53OAqTDZOoNA7Z2mSErYvYsZ46MLwKXikbb714C6iwg2l9h1w== X-YMail-OSG: xY0LLVwVM1nfTqzAhN5p.vwpiy5cSNXgakDO4B6grLAxH0sH4PGJ6oYCE452NBi GHAeiztdodSNVWuIjRTbslhrxC0_I1VP3cxNbPxwnUChbCLq8yfUMfhEgy2tO.xOKJ0j3A40lcse l4TccTSdcha.GBl3lWHW0fc9MJyXSjDTkQyuq8XNEM.PabcyF3IDj9OK_rNnqCiLNFLm6_w7qxPb rYgWqOYwIIdKZaY0Uawl9bV.rZi7CvlgTuav.X7yTlywHN4V8Rrm9Aq9hUPBWwlDqjc8w3r6EebJ il9Xy5WIRmtRXYcx.F5qjOW7Eh5xDjuP3MCGFwgcLL0Ygpzlpu4p5b1etoAzvqqCnMA4p3oaTBn6 rn1rgxtNDuSddu05igokjal_LmCE5etAtXCi6PMQjKA6ntD0ww0rMcT6n_8MRuP9QeBs7cBdoK4k PFJ8BpcT2jh4Dp1Rh3A0P_qwAVNaI0zHQ8xpzPgGm_nUr3P3xU1135AawJ8Z3v9bdgQHG99v9qiK xQ8akRKALAnD4t6lwigE6tzjfnsmN.x4g08TYQ2fT3hIRd22uXKc7LVZ.BcdITy1NLwrJ7Hy4oNQ Yx1GMa12su6KKGfBkSYQ36R06VQURJYePzCANgrVtPKgos0t9mhFHhrgwyKkv_EPjet5fKE5g3jW eLKdXnt2NLzgU7pfP0qyIj25bzmlO0knBL24X0qro7jxArMdqHKRHU6bQW.B96iRtb5q5gySFvHG 1RqqNwkjZ5YY8Sa3BXf3mJP8lMeeOR0qlIUFKH_XhSQ_UQOvRxFT6uWf0762Rs6wM9znWD8XCTE5 4tMClD9_zy.mk1QYoc6emYMsv9vilUY5.VNHhcYqduIvdmzMQyTZSOxfF_fy8o7StYQVYgDZUitD 21M9E_gZvytxFdmUtU9D9kWNCkgOZVW2h21fAEB1ncOyXQ4yvYiVwk40kxTEUaXjRqWxGM39PPpB unNzW5E_1OrnZfm.61JSE1.UPf_3xwQtMcuU34inmDge9xzPDiuZ51JHaIE.4ioTvQDuGSaqJpmI rWfu5FiSsKFBgSzHoMEUf1ZOngNt0s2cadsFgDiL6FV2o3xvjebeFcGBgfVCnskYf51.sL2a6lpO 9Ess6wZtRTpYuKxaaKHVu8i.aWIJr9SMkiIEzgyhHQ6G.5ab2rhF8pwx6R88mOo41EyNnrx4JUqS 2.KZdYw2_fmZfIYOLE0qD1ng.pG8JNpfgXZg55E_99DBPFw_lzYuZe_yvHEhBcMqlSYfIbLhMxUR ZGhX52At6RUAhqXTN1PVbqwp1qL2mFlP8pS3cuf0ADCQz1UjGtp3h1PlgxO1TaVxNewAsggIQuWi stOWax1jnmnNpm4bDE6eqG_M_eNfMy14.e6si8VaZfz5fYPCCHCoQ0PlpP4jUcV2Dil7u9zszSC7 SHtIePQfpcu1QYYo.2ArQgvOvgxtt1O63j4q2c4kJFKy.Uq78GuNSeV0sup1tSoATwyKb.cwSqn7 YXQvJi_4cn_wBseNWfe2iwZMEOiCt_ZZtFh8c0.St68VQxa1eIHWbfCoYfz7.iKNShG0El5V9nSH f8z_gMSDkfSNG228R6YC0reL92e9FhkPD0LaRChC_zCMxM9BTQNUtLd.DLc7Vv.EIDNS5ZjeAo1w pMUTnk2FOnhAMHrLz6sy5OXPbeTuIXi_u8J82lBV3PCsOs1i9ueqSTFVw8gEkK03LNLdtk_2_Tyx LzyofWLnc67cPZiJIRBHb1Ml8ZtUnL2Pq0ybcxIQYq9wIBlHOa07UJs6yd.J8qgS1QfU9dXV9Rjo mOKP40cEH0dfQVeS.bjvWyl1Zp1Xq_hlwvpCa33I_LZTikOfSsMcltszQ7jl6MdxBE2TnUyldnf5 bgnKDct5xiAY58ngrWFtS.bFpd5mKB3PnLOOdlztY.FJbKSqRTT5_5bppe8ifMHdBKX_qdR_xYu8 sZnulQhc3SS_jLtA8cxe7K4Uiz0sxHbeulQ1D.no_i5yCgEIqApyTRhmP_9Qd3XSO89vpJAmDBla c15JyLs0OisnM5h8h7wga55Sc6uIdIEHISf248XNfiECWqyS0d0OoNQ8ZG5i5xS_0kQQ0aXgpyEZ VLHQzG0LGmTt6x8AZnQpLUB.yyu3HYPR3RLkscRzIHnl_iITBLDjPK1O1Kj0VlRGbOGHN4coVg8x XgPnsMLcE5MfqQFSe91gOO6qaqLkcjfrCb9bTY2QEwrxs8p6CGv6.ok9S5zeDTs6Bsgr7GDJibw8 xDBD6TKEUhjeHmnBr_fae3UI6VMfyNlvjEmgaUcp5MEibun2JyEhyPhw7eTx5zEe59vCUioFTlUA OgIhtgNSD03cnPw-- X-Sonic-MF: X-Sonic-ID: 3af970f5-c329-4027-a2d3-0726bd1eb770 Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.gq1.yahoo.com with HTTP; Thu, 23 Oct 2025 12:34:49 +0000 Date: Thu, 23 Oct 2025 12:34:43 +0000 (UTC) From: Sergey Prokhorenko To: pgsql-hackers Message-ID: <181705213.487328.1761222883245@mail.yahoo.com> In-Reply-To: <1791665551.452444.1761209220211@mail.yahoo.com> References: <1791665551.452444.1761209220211.ref@mail.yahoo.com> <1791665551.452444.1761209220211@mail.yahoo.com> Subject: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_487327_736198340.1761222883243" X-Mailer: WebService/1.1.24562 YahooMailIosMobile Content-Length: 10601 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_487327_736198340.1761222883243 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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() impleme= ntations in PostgreSQL and several libraries. I'm writing to express my str= ong support for a new patch by Andrey Borodin, the developer of the built-i= n uuidv7() function for PostgreSQL 18. This patch adds two new functions fo= r UUID compact text representation. These functions would be long-awaited a= dditions 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:=C2=A0https://github.com/x4m/postgres_g/commit/= aa902bbc5dfc47d4b35f05016304a1e671abb505=C2=A0 _______________________________ uuid_to_base32hex ( uuid ) -> text Encodes a UUID into a 26-character base32hex string (uppercase, no hyphens,= without padding), using the alphabet 0123456789ABCDEFGHIJKLMNOPQRSTUV as s= pecified 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 *=C2=A05 bits). The additional 2 zer= o 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 co= ntexts. 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-i= nsensitive. Invalid inputs return NULL. The decoding is lossless and produc= es a bitwise-identical UUID. Example:base32hex_to_uuid('06AJBM9TUTSVND36VA87V8BVJO') -> 019535d9-3df7-79= fb-b466-fa907fa17f9e_______________________________ We considered base36 but rejected it due to poor performance. Crockford's B= ase32 was also rejected due to its lack of native support in standard libra= ries, making base32hex the most practical choice. Converter:=C2=A0https://tomeko.net/online_tools/base32hex.php?lang=3Den Best regards,Sergey Prokhorenko ------=_Part_487327_736198340.1761222883243 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi pgsql-hackers,

I'm writing to propose adding two= new built-in functions to PostgreSQL that provide compact UUID encoding us= ing the base32hex format.

I'm one of the contributors to RFC 9562 (UUIDs) and t= o the uuidv7() implementations in PostgreSQL and several libraries. I'm wri= ting to express my strong support for a new patch by Andrey Borodin, the de= veloper of the built-in uuidv7() function for PostgreSQL 18. This patch add= s two new functions for UUID compact text representation. These functions w= ould 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 commi= t.

T= he 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 464= 8 (ht= tps://datatracker.ietf.org/doc/html/rfc4648#page-10).

To accommodate base= 32hex encoding (5 bits per character), the 128-bit UUID requires 130 bits t= otal (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-const= rained contexts.

Example:
uuid_to_base32hex('0= 19535d9-3df7-79fb-b466-fa907fa17f9e'::uuid) -> 06AJBM9TUTSVND36VA87V8BVJ= O
_______________________________

base32hex_to= _uuid ( text ) -> uuid

Decodes a base32hex string back into its original UUI= D. The input is case-insensitive. Invalid inputs return NULL. The decoding = is lossless and produces a bitwise-identical UUID.

Example:
base32hex_to_uuid('06AJBM9TUTSVND36VA87V8BVJO') -> 019535d= 9-3df7-79fb-b466-fa907fa17f9e
____________= ___________________

We considered base36 but rejected it due to poor performa= nce. Crockford's Base32 was also rejected due to its lack of native support= in standard libraries, making base32hex the most practical choice.



Best regards,
Sergey = Prokhorenko


------=_Part_487327_736198340.1761222883243--