Re: [SQL] Functional Indexes

"Bryan White" <bryan@arcamax.com> writes:
> The documentation for CREATE INDEX implies that functions are allowed in
> index definitions but when I execute:
> create unique index idxtest on customer (lower(email));
> the result is:
> ERROR:  DefineIndex: (null) class not found
> Should this work?  Do I have the syntax wrong?

I tried this wih 6.4.2 and found that it was only accepted if I
explicitly identified which index operator class to use:

play=> create table customer (email text);
CREATE
play=> create unique index idxtest on customer (lower(email));
ERROR:  DefineIndex:  class not found
play=> create unique index idxtest on customer (lower(email) text_ops);
CREATE
play=>

That'll do as a workaround for Bryan, but isn't this a bug?  Surely
the system ought to know what type the result of lower() is...

            regards, tom lane