Thread: 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.