Re: Index on array element

Randolf Richardson <rr(at)8x(dot)ca> writes:
>>> How can I create an index on an array element?
>>
>> You need 7.4 and an extra layer of parentheses:
>>
>> create index foodex on foo ((textarray[3]));

> Sorry, but this isn't obvious to me as arrays in a database are a new
> concept for me -- why are the extra layer of parenthesis needed?

It's got nothing to do with arrays as such, but is purely a syntactic
restriction: if the indexed item is anything but an unadorned column
name or a function call, CREATE INDEX wants parentheses around it.

This is because the CREATE INDEX syntax also allows for an "operator
class" name in there. Consider

create index foodex on foo (bar ! ops);

Is this supposed to mean indexing the expression "bar ! ops" (infix !
operator) or indexing the expression "bar !" (postfix ! operator) using
the operator class "ops"? No way to tell, so we make you clarify your
intent with parentheses. You can say either

create index foodex on foo ((bar ! ops));
create index foodex on foo ((bar !) ops);

depending which interpretation you want.

regards, tom lane