pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunique index on function and column
5+ messages / 3 participants
[nested] [flat]
* unique index on function and column
@ 2002-06-25 17:58 Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: Bruno Wolff III @ 2002-06-25 17:58 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unique index on function and column
@ 2002-06-25 18:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2002-06-25 18:35 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unique index on function and column
@ 2002-06-25 18:45 Bruno Wolff III <bruno@wolff.to>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2002-06-25 18:45 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unique index on function and column
@ 2002-06-25 20:09 Josh Berkus <josh@agliodbs.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-06-25 20:09 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: unique index on function and column
@ 2002-06-25 20:34 Bruno Wolff III <bruno@wolff.to>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2002-06-25 20:34 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-06-25 20:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-25 17:58 unique index on function and column Bruno Wolff III <bruno@wolff.to>
2002-06-25 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-25 18:45 ` Bruno Wolff III <bruno@wolff.to>
2002-06-25 20:09 ` Josh Berkus <josh@agliodbs.com>
2002-06-25 20:34 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox