Re: [SQL] Functional Indexes

"Bryan White" <bryan(at)arcamax(dot)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