Re: digest data types?

On Apr 12, 2007, at 3:40 PM, Reece Hart wrote:

> I've asked a question about message digests data types on pgsql-
> general
> several times. No one's answered. It reminds me of a childhood refrain
> "No one likes me, everyone hates me, I'm going to eat some worms".
> Anyway, I'm reposting the question here.
>
>
> 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 could use bytea, or do a custom data type (which wouldn't be
that hard to do, but not very exciting).

>
> 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?

It'll help, but how much depends on the details. All but the simplest
data types tend to be "varlena" types, so an MD5 stored as text is
going to eat 32+4 bytes as hex text or 16+4 as bytea, or 16 as a custom
type. But the row header is 24 bytes or so, so if you were to have a
table with a single MD5 field and an integer key, the difference between
hex text, bytea and a custom field would be 24+4+32+4 : 24+4+16+4 :
24+16 +4, or 64 : 48 : 44 .[1]

Base64 or base85 encoding would get you something between
hex and bytea encoding, and leave you with something nominally
readable as text.

Cheers,
Steve

[1] All numbers are figments of my imagination, check with
current source code for accurate numbers.