pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex creation question for expression (col1 || '-' || col2)
5+ messages / 4 participants
[nested] [flat]
* Index creation question for expression (col1 || '-' || col2)
@ 2005-07-08 16:08 Ying Lu <ying_lu@cs.concordia.ca>
0 siblings, 3 replies; 5+ messages in thread
From: Ying Lu @ 2005-07-08 16:08 UTC (permalink / raw)
To: pgsql-sql
Greetings,
A question about creating index for the following expression.
CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
May I know is it possible and how I may create index for the expression
such as "col1 || '-' || col2" for a table please?
Thanks a lot,
Emi
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index creation question for expression (col1 || '-' || col2)
@ 2005-07-08 16:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ying Lu <ying_lu@cs.concordia.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-07-08 16:15 UTC (permalink / raw)
To: Ying Lu <ying_lu@cs.concordia.ca>; +Cc: pgsql-sql
Ying Lu <ying_lu@cs.concordia.ca> writes:
> A question about creating index for the following expression.
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
You need more parentheses:
CREATE INDEX idx_t1 ON test ((col1 || '-' || col2));
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index creation question for expression (col1 || '-' || col2)
@ 2005-07-08 16:32 Michael Fuhr <mike@fuhr.org>
parent: Ying Lu <ying_lu@cs.concordia.ca>
2 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2005-07-08 16:32 UTC (permalink / raw)
To: Ying Lu <ying_lu@cs.concordia.ca>; +Cc: pgsql-sql
On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote:
>
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
>
> May I know is it possible and how I may create index for the expression
> such as "col1 || '-' || col2" for a table please?
See "Indexes on Expressions" in the documentation:
http://www.postgresql.org/docs/8.0/static/indexes-expressional.html
"The syntax of the CREATE INDEX command normally requires writing
parentheses around index expressions, as shown in the second
example...."
That second example is exactly what you're trying to do:
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index creation question for expression (col1 || '-' || col2)
@ 2005-07-08 16:57 Bruno Wolff III <bruno@wolff.to>
parent: Ying Lu <ying_lu@cs.concordia.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2005-07-08 16:57 UTC (permalink / raw)
To: Ying Lu <ying_lu@cs.concordia.ca>; +Cc: pgsql-sql
On Fri, Jul 08, 2005 at 12:08:41 -0400,
Ying Lu <ying_lu@cs.concordia.ca> wrote:
> Greetings,
>
> A question about creating index for the following expression.
>
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
>
> May I know is it possible and how I may create index for the expression
> such as "col1 || '-' || col2" for a table please?
You have already received some answers to this specific question.
However, you might consider a different solution to the underlying
problem. You can make a multicolumn index that for many purposes would
be better than the above functional index.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Index creation question for expression (col1 || '-' ||
@ 2005-07-08 17:08 Ying Lu <ying_lu@cs.concordia.ca>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 5+ messages in thread
From: Ying Lu @ 2005-07-08 17:08 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
>On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote:
>
>
>>CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
>>
>>May I know is it possible and how I may create index for the expression
>>such as "col1 || '-' || col2" for a table please?
>>
>>
>
>
>"The syntax of the CREATE INDEX command normally requires writing
>parentheses around index expressions, as shown in the second
>example...."
>
>That second example is exactly what you're trying to do:
>
>CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
>
>
This is exactly what I want. I missed a pair of "()" :( . That is why I
got an error.
Thank you very much.
- Emi
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-07-08 17:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-08 16:08 Index creation question for expression (col1 || '-' || col2) Ying Lu <ying_lu@cs.concordia.ca>
2005-07-08 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-08 16:32 ` Michael Fuhr <mike@fuhr.org>
2005-07-08 17:08 ` Re: Index creation question for expression (col1 || '-' || Ying Lu <ying_lu@cs.concordia.ca>
2005-07-08 16:57 ` 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