Thread: Indexes on Arrays

Hello everyone.  Hopefully someone can give me an answer and workaround on
this...

I have a table with an array like so:
create table foo (    id    int4,    names    varchar(80)[]);

and would like to place an index on name[1].  I try
create table foo_idx1 on foo (names[1]);

but I get...
ERROR:  parser: parse error at or near "["

Thoughts and suggestions are welcome.

- K

Kristofer Munn * KMI * 732-254-9305 * AIM KrMunn * http://www.munn.com/

Kristofer Munn <kmunn@munn.com> writes:
> I have a table with an array like so:
>     create table foo (
>         id    int4,
>         names    varchar(80)[]
>     );
> and would like to place an index on name[1].

Right now the only way is to make a function that extracts names[1]
and create a functional index on yourfunction(names).

Although functional indexes are a perfectly good general solution from
an academic point of view, this is still a pain in the neck :-(.
Also kinda slow, unless you code the function in C.

I recall talking to someone who was going to look at supporting more
general expressions for index values, but nothing's come of the idea
so far.
        regards, tom lane