Invalid byte sequences when hashing text in Postgres

Question

I want to hash some strings (character varying) in Postgres, but it fails on some strings.

This works:

select encode(sha256('abc'), 'hex') as hash

ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad

But this fails:

select encode(sha256('\/'), 'hex') as hash

ERROR: invalid input syntax for type bytea LINE 3: select encode(sha256('/'), 'hex') as hash ^ SQL state: 22P02 Character: 24

How can I make my hashing work with any string?

Laurenz Albe · Accepted Answer · 2021-10-01 11:27:33Z

1

'\/' is not a valid bytea literal. Use convert_to:

SELECT encode(sha256(convert_to('\/', 'UTF8')), 'hex') AS hash;

answered Oct 1, 2021 at 11:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Answer 1