Re: Best way to use indexes for partial match at

On Thu, 2005-11-10 at 11:27, Peter Eisentraut wrote:
> Scott Marlowe wrote:
> > Ya know, this brings up an interesting question, would it be feasible
> > to allow for a function of somekind to be applied as an argument to a
> > primary key declaration?
>
> You would have to guarantee somehow that the function is a one-to-one
> mapping, in order not to destroy the integrity of the primary key
> constraint. I cannot think of any useful functions that fulfill this
> criterion.

No, I wouldn't think one to one would be necessary. If you had a
primary key that was case insensitive, for example, it would not map one
to one. It would, in fact, be MORE greedy about matching, so that you
could not have both a "Peter Eisentraut" and a "peter eisentraut" in the
same table.

While I could easily add a unique on table (lower(namefield)) to get
this, being able to use a lower() or some other function would be very
useful. I don't see one to one being necessary, it just needs to be
consistent.

>
> > It would certainly be useful for character
> > type pks in non-C locales.
>
> I don't see how.

By only having to maintain one index on a large table instead of having
a PK AND a separate unique index for these kinds of cases.