Re: Index on array element

Steve Crawford <scrawford@pinpointresearch.com> writes:

> How can I create an index on an array element? I seem to recall having
> done this in the past but I don't recall how.
>
> steve=# \d foo
>        Table "public.foo"
>   Column   |  Type  | Modifiers
> -----------+--------+-----------
>  textarray | text[] |
>
> steve=# create index foodex on foo (textarray[3]);
> ERROR:  parser: parse error at or near "[" at character 38
>
> steve=# select version();
> PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3
> 20030226 (prerelease) (SuSE Linux)

In 7.3 you'll have to create a function to make this index.

  create function first(text[]) returns text language sql as 'select $1 [1]' strict immutable;

then you can create an index like

  create index foodex on foo (first(textarray));

In 7.4 you can do arbitrary expressions, but in 7.3 you can only do simple
function calls of a single column.

--
greg