Thread: digest data types?

Does anyone have postgresql types for message digests, especially md5 and sha1?

Obviously I could store these as text (as I currently do), but I'm particularly interested in custom types that store digests as binary blobs and provide conversion to/from text.

Am I correct in assuming that the space saved by storing digests as binary (1/2 size of hex) will substantially impact index ins/upd/del performance or when the digest itself is a large fraction of the rest of the row size?

Thanks,
Reece

-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Wed, 2007-04-11 at 14:41 -0700, Reece Hart wrote:
> Does anyone have postgresql types for message digests, especially md5
> and sha1?
>
> Obviously I could store these as text (as I currently do), but I'm
> particularly interested in custom types that store digests as binary
> blobs and provide conversion to/from text.
>

You can just do something like:

INSERT INTO mytable(mycolumn) VALUES(decode(md5('foo'), 'hex')::bytea);
and
SELECT encode(mycolumn, 'hex')::text AS md5 FROM mytable;

and store that in a BYTEA column. You can make your own type that does
something similar without much effort.

> Am I correct in assuming that the space saved by storing digests as
> binary (1/2 size of hex) will substantially impact index ins/upd/del
> performance or when the digest itself is a large fraction of the rest
> of the row size?
>

Probably won't have much of an impact. The per-row overhead is larger
than the size of a single md5 hash. If this is a major aspect of your
performance than it might make some difference.

Regards,
    Jeff Davis