Postgres index with sha() function in expression

user_4300087 0
Viewed 145

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) → \xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f

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.

database-indexes hash postgresql
1 Answers

Double the backslash:

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