On Tue, Jun 14, 2005 at 15:54:50 -0500,
Peter Fein <pfein@pobox.com> wrote:
>
> I'm unclear why I'd need to store the hash in a column. I suppose I
> could have the hash column populated by a trigger on inserts, but this
> seems to get me the same functionality & is less obvious.
>
> For the archives, I did:
>
> CREATE UNIQUE INDEX idx_md5_sometext ON mytable USING btree
> (group_id, md5(sometext))
> WHERE group_representative = true;
>
> I then basically replicate this in a SELECT on the client side
> (including calculating the MD5 by the client) to figure out the correct
> value for group_representative before inserting a new row. This is the
> only way I use the MD5, so I don't much care about retrieving it in
> other contexts.
That should work fine.
I wasn't sure that you weren't going to want to use the hash for joins.
And I was a little concerned that because you used the phrase "hash index",
that you might be considering using a hash (as opposed to btree) index.