Re: md5() sorting

On Nov 12, 2007 1:53 PM, Lew <lew@lwsc.ehost-services.com> wrote:
> Karsten Hilbert wrote:
> > On Wed, Nov 07, 2007 at 05:36:47PM +0200, Marko Kreen wrote:
> >
> >>>> I'm wondering if you cast the md5sum as a bytea instead of text and
> >>>> then sort, if that would solve it simply.
> >>> Along the lines of
> >>>
> >>>         ... ORDER BY decode(md5('...'), 'hex');
> >>>
> >> Maybe using digest(.., 'md5') function from pgcrypto would be better?
> >> It gives bytea immidiately.
> >
> > Sounds better at first, yes, but requires pgcrypto to be
> > installed on machines onto which GNUmed is to be deployed.
> >
> > Thanks for the suggestion, though.
>
> Since an MD5 hash is a byte array, using text (or any locale-encoded
> representation) of it is a mistake.  Hashes are not strings and shouldn't be
> treated as such.

Well, the output of md5() on postgresql is not binary but a hex
representation, so it's not that big of a deal really.