Re: pgcrypto : how to get SHA1(string) as a 40-char string, NOT binary string?

On Wed, Jul 13, 2005 at 12:45:19AM -0700, Miles Keaton wrote:
> I have the contrib/pgcrypto installed.
> I want to get the 40-character hash from SHA1
>
> Example: SELECT digest('blue', 'sha1') would be:
> 4c9a82ce72ca2519f38d0af0abbb4cecb9fceca9
>
> I was surprised and disappointed to get a binary-hash back.

The pgcrypto documentation does show that the return type is bytea,
and it says that the function "Returns binary hash."

> Does anyone know how to get the regular 40-character string back from
> SHA1 instead of the binary-hash? (Or how to convert a binary-hash
> into a-z0-9 chars?)

Use encode().

http://www.postgresql.org/docs/8.0/static/functions-binarystring.html

SELECT encode(digest('blue', 'sha1'), 'hex');
encode
------------------------------------------
4c9a82ce72ca2519f38d0af0abbb4cecb9fceca9
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/