Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: Re: Can we get sha* function over text, that could be used in index?
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Linus Heckemann" <linus@schreibt.jetzt>
Cc: "pgsql-general mailing list" <pgsql-general@postgresql.org>
In-Reply-To: <ygawm0a9bh9.fsf@localhost>
Date: Fri, 20 Feb 2026 22:40:37 +0100
Message-Id: <63a9ec31-8a5f-47c7-8a70-f1c57207ffd3@manitou-mail.org>
Archived-At: <https://www.postgresql.org/message-id/63a9ec31-8a5f-47c7-8a70-f1c57207ffd3%40manitou-mail.org>
Precedence: bulk

	Linus Heckemann wrote:

> - there is a byte-array representation of text columns, which appears to
>  be independent of database encoding

Not sure what you're refering to. Both the on-disk and in-memory
representations of text/varchar are encoding-dependent.

> The obvious (to a naive user, like I was) approach, casting to bytea,
> has exceptionally surprising behaviour: for many text strings, it does
> exactly what the naive user might hope for, giving back the UTF-8
> representation. But multiple distinct text strings, like '\033' and
> '\x1b', convert to the same byte string! And text strings containing a
> backslash that doesn't fit the bytea hex format or the bytea escape
> format will fail to convert completely!

Yes. It seems a common mistake to forget or ignore that
backslashes are special in the input text representation of bytea.
It might be not obvious from reading the doc at [1]
but we just need to quote backslashes by doubling them.

AFAIK a working solution for the OP would be:
 sha256(replace(colname, '\', '\\')::bytea)

The result is encoding-dependent, but that does not matter
in the context of an expression. index.
If the database ever needs to change its encoding, it will have to
be recreated entirely anyway.


[1]
https://www.postgresql.org/docs/current/datatype-binary.html#DATATYPE-BINAR=
Y-BYTEA-ESCAPE-FORMAT

Best regards,
--=20
Daniel V=E9rit=E9=20
https://postgresql.verite.pro/