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/