pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCan we get sha* function over text, that could be used in index?
8+ messages / 6 participants
[nested] [flat]
* Can we get sha* function over text, that could be used in index?
@ 2026-02-18 14:58 hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 8+ messages in thread
From: hubert depesz lubaczewski @ 2026-02-18 14:58 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-18 15:07 Ron Johnson <ronljohnsonjr@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 8+ messages in thread
From: Ron Johnson @ 2026-02-18 15:07 UTC (permalink / raw)
To: pgsql-general
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!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-18 18:02 Linus Heckemann <linus@schreibt.jetzt>
0 siblings, 1 reply; 8+ messages in thread
From: Linus Heckemann @ 2026-02-18 18:02 UTC (permalink / raw)
To: pgsql-general
Hi, I'm the one who asked :)
Ron Johnson wrote:
> On Wed, Feb 18, 2026 at 9:58 AM hubert depesz lubaczewski <depesz(at)depesz(dot)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).
I indeed don't need cryptographically-secure hashing in this
scenario, and I will be using md5.
Nevertheless, I think using md5 in any context that isn't "to verify
digests produced in the past" is a poor decision, and postgres should
make using other hashes just as easy!
The specific context of hashing aside, it seems weird to me that:
- there is a byte-array representation of text columns, which appears to
be independent of database encoding
- there doesn't seem to be _any_ sane way to access this.
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!
The fact that convert_to() is only stable and not immutable makes sense
to me given the effect that configuration can have on its behaviour, but
given that there does appear to be a trivially-accessible UTF-8
representation (as used by md5()) I think there should be an immutable
function that provides access to it? Is there a good reason not to? I'd
be willing to send a patch for it myself.
Linus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-19 14:48 Peter Eisentraut <peter@eisentraut.org>
parent: hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2026-02-19 14:48 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-19 22:30 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 1 reply; 8+ messages in thread
From: Peter J. Holzer @ 2026-02-19 22:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
[application/pgp-signature] signature.asc (832B, ../../mn34iiibel26qrs5k2nk3q3jjcvmt4pm6fn46vatlhdbch42yp@upwkzysveh6j/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-20 21:40 Daniel Verite <daniel@manitou-mail.org>
parent: Linus Heckemann <linus@schreibt.jetzt>
0 siblings, 1 reply; 8+ messages in thread
From: Daniel Verite @ 2026-02-20 21:40 UTC (permalink / raw)
To: Linus Heckemann <linus@schreibt.jetzt>; +Cc: pgsql-general
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-BINARY-BYTEA-ESCAPE-FORMAT
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-21 09:54 Linus Heckemann <linus@schreibt.jetzt>
parent: Daniel Verite <daniel@manitou-mail.org>
0 siblings, 0 replies; 8+ messages in thread
From: Linus Heckemann @ 2026-02-21 09:54 UTC (permalink / raw)
To: Daniel Verite <daniel@manitou-mail.org>; +Cc: pgsql-general
"Daniel Verite" <daniel@manitou-mail.org> writes:
>> - 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.
Huh, I thought I'd come to the opposite conclusion from testing the
other day (md5('ÿ') giving the same results for both a utf-8 database
and a latin1 database), but I must have been holding something wrong,
because trying again does confirm this. Sorry about that!
>> 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)
That's so simple that it feels like it should have been
obvious... Thanks! Nevertheless, it feels like a weird and potentially
computationally expensive contortion for getting from a text value to
its bytea representation.
Given their encoding-dependence, shouldn't md5() (and ::bytea, for that
matter) be marked only STABLE, not IMMUTABLE? Or is IMMUTABLE's
> guaranteed to return the same results given the same arguments forever
only valid within the context of a single database?
md5() and ::bytea aside, it seems like it would be good to have both
- a function for accessing the bytea representation of a text value
without any extra steps, and
- an IMMUTABLE function for getting a UTF-8 (specifically UTF-8,
because it's the only encoding with a character set encompassing all
other supported character sets) bytea representation of a text value
regardless of the database encoding?
Cheers
Linus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Can we get sha* function over text, that could be used in index?
@ 2026-02-21 12:40 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 8+ messages in thread
From: Peter J. Holzer @ 2026-02-21 12:40 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
[application/pgp-signature] signature.asc (832B, ../../owheexmogjkdf2uh4johukkwsi66wkixxfgnxqa77baftwsr25@sfcuuz3ikojv/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-02-21 12:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-18 14:58 Can we get sha* function over text, that could be used in index? hubert depesz lubaczewski <depesz@depesz.com>
2026-02-18 15:07 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-02-19 14:48 ` Peter Eisentraut <peter@eisentraut.org>
2026-02-19 22:30 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2026-02-21 12:40 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2026-02-18 18:02 Re: Can we get sha* function over text, that could be used in index? Linus Heckemann <linus@schreibt.jetzt>
2026-02-20 21:40 ` Daniel Verite <daniel@manitou-mail.org>
2026-02-21 09:54 ` Linus Heckemann <linus@schreibt.jetzt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox