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
