Обсуждение: unique index on function and column

Is there a simple way to do something like the following:
create unique index inst_u_app on inst (lower(host), psport);

It looks like you can have an index on several columns, but not
several functions.

Bruno Wolff III <bruno@wolff.to> writes:
> Is there a simple way to do something like the following:
> create unique index inst_u_app on inst (lower(host), psport);

> It looks like you can have an index on several columns, but not
> several functions.

The standard answer is to make a custom function that accepts all the
columns and produces a result you can index.

This is sort of an academic "an existence proof is good enough" answer,
but it *is* possible to get the results you want.  Making it more
convenient hasn't risen to the top of anyone's to-do list.
        regards, tom lane

On Tue, Jun 25, 2002 at 14:35:16 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > Is there a simple way to do something like the following:
> > create unique index inst_u_app on inst (lower(host), psport);
> 
> > It looks like you can have an index on several columns, but not
> > several functions.
> 
> The standard answer is to make a custom function that accepts all the
> columns and produces a result you can index.
> 
> This is sort of an academic "an existence proof is good enough" answer,
> but it *is* possible to get the results you want.  Making it more
> convenient hasn't risen to the top of anyone's to-do list.

It probably isn't worth the trouble in this case. The number of rows is small
and I will use a unique index without forcing a case independpent comparison.
For the time being this is good enough.

Thanks for confirming that I was reading the manually correctly.

Bruno,

> Is there a simple way to do something like the following:
> create unique index inst_u_app on inst (lower(host), psport);
>
> It looks like you can have an index on several columns, but not
> several functions.

So?  Create two seperate indexes.

Multi-column indexes are almost always less useful that you'd expected,
anyway.

--
-Josh Berkus

On Tue, Jun 25, 2002 at 13:09:25 -0700, Josh Berkus <josh@agliodbs.com> wrote:
> Bruno,
> 
> > Is there a simple way to do something like the following:
> > create unique index inst_u_app on inst (lower(host), psport);
> > 
> > It looks like you can have an index on several columns, but not
> > several functions.
> 
> So?  Create two seperate indexes.
> 
> Multi-column indexes are almost always less useful that you'd expected, 
> anyway.

I was using it to implement a constraint, not to do searches. Two separate
indexes wouldn't work for that.