Date: Thu, 19 Feb 2026 23:30:17 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Can we get sha* function over text, that could be used in index?
Message-ID: <mn34iiibel26qrs5k2nk3q3jjcvmt4pm6fn46vatlhdbch42yp@upwkzysveh6j>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <aZXTnH4uhMvVOEAg@depesz.com>
 <3dbae091-6168-4b4b-91dc-4bf1a3a51136@eisentraut.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="fv2rd27vga4pfgxc"
Content-Disposition: inline
In-Reply-To: <3dbae091-6168-4b4b-91dc-4bf1a3a51136@eisentraut.org>
Archived-At: <https://www.postgresql.org/message-id/mn34iiibel26qrs5k2nk3q3jjcvmt4pm6fn46vatlhdbch42yp%40upwkzysveh6j>
Precedence: bulk


--fv2rd27vga4pfgxc
Content-Type: text/plain; protected-headers=v1; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Subject: Re: Can we get sha* function over text, that could be used in index?
MIME-Version: 1.0

On 2026-02-19 15:48:33 +0100, Peter Eisentraut wrote:
> On 18.02.26 15:58, hubert depesz lubaczewski wrote:
> > And while I kinda undestand, create-conversion, server-encoding, I don't
> > really*grok* why we can't have immutable conversion to bytea. And/or
> > versions of sha* functions that simply work on text.
>=20
> Hash functions fundamentally work on a sequence of bytes, so bytea is the
> right type.  The encoding of text into bytes is complicated,

Maybe, but it needs to be done anyway, sicne text is ultimately stored
as a sequence of bytes on disk and sent as a sequence of bytes over the
wire. So the code should be present already.

Something like
    encode(s text, enc text) -> bytea
        Encodes s in encoding enc. E.g. encode('Tr=F6t!', 'utf-8')
        produces \x5472c3b67421
    decode(d bytea, enc text) -> text
        Decodes d assuming encoding enc. E.g.
        decode('\x5472c3b67421'::bytea, 'utf-8') produces 'Tr=F6t!'.
might be generally useful.

> so it seems better if you handle that yourself depending on the local
> requirements.

I would probably do that kind of processing in the application code,
but I can see that one might want it in the database.

        hjp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--fv2rd27vga4pfgxc
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmmXjvIACgkQ8g5IURL+
KF1/hA//atRHeLv0El7pLx8SGXZBqYlLM8PbgvbdxYufAKbcbBfiAhhgG2uykjdv
YncTYjPDniy0OncsFEL/zuWZx0oaJsAO/i9q2Nj3jfXs37EXnhq3zXNJ3cCpwX4Q
JVm+lMojOKzDJaETjEU3b2yRl4sntu1gYiAyNnAMK3RdT2kxzwVOQL064wGkOiiW
uMcCPTE177ocDhJKZsuN6PHLKflCkapkKcVZMhEOH63zRsD8hou3NF32voTioRPr
bR0WrYTyewfW7vfzN8IPgtJdfStl5jL5nkyGS26NtVPoM2pAGKwSxCGdp1OOenqs
i+I1wiu4vIFbBCn0DR40UvESe4aUZ7WTL5NvwZLA1fITusuN7Y/VF1YaS6vmqgRH
XZaMGKYVwjUJyl9yz0GwcaYLFMMmY+znsQAlVnNmolhfikMWlX2QFXgsTyYK8OHs
5v/Gn+4qMd/wFlaPcWYnGE0X2mo2fVepnKkii2lKZggcCXRqTlQOKjGpBJuAUO+4
7x/KF5CU0Y6oAPm9/Uzd+vIUgQTF6BlBkwLFX55jUVP+TTWO0TNIkrtl8gMzW3hV
dizyi0GXwESHUOzsK/Sqeewmx5r23T93A6EYE9yCpblgpI8ACELU1/8awCr+6uSA
X0LO+jJ1tLIaucUWxvnanEdylPfmr8R3JGoHXcsRYPvjAJWDwck=
=GfGk
-----END PGP SIGNATURE-----

--fv2rd27vga4pfgxc--