postgresql_hashes_stored_as_binary_cheatsheet.sql

postgresql_hashes_stored_as_binary_cheatsheet.sql

WITH xs as (
  select '' as x -- empty string
  union all select 'a'  -- lowercase letter
  union all select 'A' -- uppercase
  union all select 'aa' -- two ascii letters
  union all select 'á' -- 'a' with accent
  union all select '的' -- one CJK character, stored as multiple bytes
  union all select 'deadbeef'
  union all select 'DEADBEEF'
)
select
    x
  , length(x)             as readable_char_len
  , octet_length(x)       as byte_len
--   , md5(x)::bytea         as md5_bin_THIS_IS_WRONG
  , decode(md5(x), 'hex') as md5_bin -- RIGHT
  , octet_length(decode(md5(x), 'hex')) as md5_bin_bytelen
  , encode(decode(md5(x), 'hex'),'hex') as md5hex_to_md5bin_to_md5hex_roundtrip
  , md5(x)                as md5_hex
  , md5(x)=encode(decode(md5(x), 'hex'),'hex') unittest
from xs
;