Thread

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

   hubert depesz lubaczewski <depesz@depesz.com> — 2026-02-18T14:58:36Z

   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
   
   
   
   
   

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

   Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-18T15:07:22Z

   On Wed, Feb 18, 2026 at 9:58 AM hubert depesz lubaczewski <depesz@depesz.com>
   wrote:
   
   > 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().
   
   
   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!
   

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

   Peter Eisentraut <peter@eisentraut.org> — 2026-02-19T14:48:33Z

   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, so it 
   seems better if you handle that yourself depending on the local 
   requirements.
   
   
   
   
   

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

   Peter J. Holzer <hjp-pgsql@hjp.at> — 2026-02-19T22:30:17Z

   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@hjp.at         |    -- Charles Stross, "Creative writing
   __/   | http://www.hjp.at/ |       challenge!"
   

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

   Peter J. Holzer <hjp-pgsql@hjp.at> — 2026-02-21T12:40:55Z

   On 2026-02-19 23:30:17 +0100, Peter J. Holzer wrote:
   > Something like
   >     encode(s text, enc text) -> bytea
   >     decode(d bytea, enc text) -> text
   > might be generally useful.
   
   Please ignore this email. These are basically the same as convert_to()
   and convert_from() which already exist.
   
           hjp
   
   
   -- 
      _  | Peter J. Holzer    | Story must make more sense than reality.
   |_|_) |                    |
   | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
   __/   | http://www.hjp.at/ |       challenge!"