Re: Can we get sha* function over text, that could be used in index?

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.
>
> 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öt!', 'utf-8')
produces \x5472c3b67421
decode(d bytea, enc text) -> text
Decodes d assuming encoding enc. E.g.
decode('\x5472c3b67421'::bytea, 'utf-8') produces 'Tröt!'.
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

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