Postgres index with sha() function in expression

Question

The sha functions in postgres only take bytea as input type.

sha512 ( bytea ) → bytea
    Computes the SHA-512 hash of the binary string.
    sha512('abc'::bytea) → \xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f

The problem is that casting like shown in the documentation ('abc'::bytea) does not work if the input text contains a backslash (Error: invalid input syntax for type bytea). And the only way i could find to convert such a string to bytea is this function:

convert_to ( string text, dest_encoding name ) → bytea
  Converts a text string (in the database encoding) to a binary string encoded in encoding dest_encoding (see Section 23.3.4 for available conversions).
  convert_to('some_text', 'UTF8') → \x736f6d655f74657874

But this function is not immutable thus it can't be used in a UNIQUE INDEX like this one:

CREATE UNIQUE INDEX index_name ON public.table
  USING btree
  (
    (md5(message)), -- works
    (sha512(convert_to(message, 'UTF8'))) -- not immutable
  );

Question: How to use the sha functions in a index expression? I don't want to use md5.

Laurenz Albe · Accepted Answer · 2021-07-13 14:59:27Z

3

Double the backslash:

CREATE UNIQUE INDEX index_name ON public.table
  USING btree
  (
    (sha512(replace(message, '\', '\\')::bytea))
  );

answered Jul 13, 2021 at 14:59

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FireEmerald Over a year ago

I'm wondering if this will work in any case - aren't there other identifiers which result in similar errors when casting to bytea? It looks really ugly.

Laurenz Albe Over a year ago

Yes, that is safe - read byteain in src/backend/utils/adt/varlena.c. Sorry if it doesn't appeal to your sense of esthetics.

FireEmerald Over a year ago

Found varlena.c source here. I expected the usage example sha512('abc'::bytea) → ... to work properly for all types of input string or at least a note on the page would be good. There is also another function digest('\bla', 'sha512') of the pgcrypto module which handles the escaping.

Collectives™ on Stack Overflow

Postgres index with sha() function in expression

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related