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

From: hubert depesz lubaczewski <[email-masked]>
Date: Wed, 18 Feb 2026 15:58:36 +0100

Hi,
So, we have various sha* functions.

And I recently got asked about using them as a based for unique index
over long texts.
Normally one would do it with md5(text), but the person asking wanted to
use sha(). and these functions work only on bytea.

And apparently - we can't.

'text-value'::bytea won't work for some specific text values.
convert_to() isn't immutable.

I figured out that I can do something like:

SELECT
    sha256(
        string_agg( ascii( t )::text, ',' ORDER BY idx )::bytea
    )
FROM
    regexp_split_to_table( 'INPUT_STRING', '' ) WITH ORDINALITY AS x ( t, idx );

But that's hardly sane solution.

I've read bug report from 2008:
https://www.postgresql.org/message-id/flat/48D20645.1090503%40gmx.net#ce27df4802c9854a9eb77066a5c7cb05

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.

Is it doable? How does it work in md5()? Apparently it does also work in
pgcrypto/digest(), so there should be a way to get it in core sha*
functions?

Best regards,

depesz

---

From: Ron Johnson <[email-masked]>
Date: Wed, 18 Feb 2026 10:07:22 -0500

On Wed, Feb 18, 2026 at 9:58 AM hubert depesz lubaczewski <[email-masked]>
wrote:

> use sha().

I think I'd push back, asking them if they really need
cryptographically-secure hashing (which they most probably don't).

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 19 Feb 2026 15:48:33 +0100

On 18.02.26 15:58, hubert depesz lubaczewski wrote:
> really*grok* why we can't have immutable conversion to bytea. And/or

Hash functions fundamentally work on a sequence of bytes, so bytea is 
the right type.  The encoding of text into bytes is complicated, so it 
seems better if you handle that yourself depending on the local 
requirements.

---

From: "Peter J. Holzer" <[email-masked]>
Date: Thu, 19 Feb 2026 23:30:17 +0100

On 2026-02-19 15:48:33 +0100, Peter Eisentraut wrote:
> 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.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 21 Feb 2026 13:40:55 +0100

> might be generally useful.

Please ignore this email. These are basically the same as convert_to()
and convert_from() which already exist.

        hjp