public inbox for [email protected]  

help / color / mirror / Atom feed

From: hubert depesz lubaczewski <[email protected]>
To: pgsql-general mailing list <[email protected]>
Subject: Can we get sha* function over text, that could be used in index?
Date: Wed, 18 Feb 2026 15:58:36 +0100
Message-ID: <[email protected]> (raw)

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#ce27df4802c9854a9eb77066a5c7cb...

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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Can we get sha* function over text, that could be used in index?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox