Postgres Pro
Downloads
Home   >   mailing lists

Thread: Index on computed column

Index on computed column

From
han.holl@pobox.com (Han Holl)
Date:
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

Re: Index on computed column

From
Alvaro Herrera
Date:
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)

Re: Index on computed column

From
Stephan Szabo
Date:
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.

Re: Index on computed column

From
Kris Jurka
Date:
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