By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,332 Members | 1,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,332 IT Pros & Developers. It's quick & easy.

Index on computed column

P: n/a
Hi,

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

palga=> select rubriek(rapport, lseek, 'naamvrouw',0) from main
where rapport = 'T098-20900';
rubriek
-----------
Ramrattan
(1 row)
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.

Thanks in advance,

Han Holl
Nov 23 '05 #1
Share this Question
Share on Google+
7 Replies

P: n/a
On Tue, Apr 20, 2004 at 12:48:42PM -0700, Han Holl wrote:
Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:


Functional indexes could not have constants in 7.3; you can build a
wrapper function with the constant embedded, and create an index using
the wrapper function.

Or you can just use 7.4.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

P: n/a
On Tue, Apr 20, 2004 at 12:48:42PM -0700, Han Holl wrote:
Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:


Functional indexes could not have constants in 7.3; you can build a
wrapper function with the constant embedded, and create an index using
the wrapper function.

Or you can just use 7.4.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a

On Tue, 20 Apr 2004, Han Holl wrote:
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.


You cannot create functional indexes with a constant in the 7.3 series.
This capability was only added in the 7.4 series. You can work around
this problem by creating a wrapper function that calls the real function
with the constants and creating the index on the wrapper function.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

P: n/a

On Tue, 20 Apr 2004, Han Holl wrote:
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.


You cannot create functional indexes with a constant in the 7.3 series.
This capability was only added in the 7.4 series. You can work around
this problem by creating a wrapper function that calls the real function
with the constants and creating the index on the wrapper function.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5

P: n/a
On Tue, 20 Apr 2004, Han Holl wrote:
Hi,

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

palga=> select rubriek(rapport, lseek, 'naamvrouw',0) from main
where rapport = 'T098-20900';
rubriek
-----------
Ramrattan
(1 row)
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.


In 7.3, functional indexes can only be across columns of the table, so
something like the above is illegal. You can get around this my making a
function that hardcodes the constants and then use that in the index and
queries.

In 7.4, you can index a more general set of expressions.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

P: n/a
On Tue, 20 Apr 2004, Han Holl wrote:
Hi,

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

palga=> select rubriek(rapport, lseek, 'naamvrouw',0) from main
where rapport = 'T098-20900';
rubriek
-----------
Ramrattan
(1 row)
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.


In 7.3, functional indexes can only be across columns of the table, so
something like the above is illegal. You can get around this my making a
function that hardcodes the constants and then use that in the index and
queries.

In 7.4, you can index a more general set of expressions.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #7

P: n/a
Hello Alvaro, Kris and Stephan,

Thank you for your help. I apologize for reacting so late, but I've
been on vacation.
I must say I'm amazed that I post a question on April 20, and get
three almost identical, equally helpful, reactions after six days,
posted in the same hour!

I'm puzzled but grateful,

Han Holl
Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.