Re: BUG #3965: UNIQUE constraint fails on long column values

Juho Saarikko wrote:
> I suggest implementing unique hash indexes and automatically creating one
> (and turning the b-tree index into a non-unique one) when a large value is
> inserted to fix this. Alternatively, fix b-trees so they can handle large
> values; however, a hash index should be far more efficient for this specific
> case, since the size of a hash is independent of pre-hash data size.

The current implementation of hash indexes actually store the whole key,
in addition to the hash, so the size of the hash index is not
independent of the key size. There has been some discussion on revamping
the hash index implementation, and changing that among other things, but
don't hold your breath.

As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a
pretty good work-around.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com