I have a table defined like this:

scott=> \d t
Table "public.t"
Column | Type | Modifiers
--------+----------------------+-----------
col1 | character varying(3) |
col2 | integer[] |
Indexes:
"ind_t_proj" btree (proj_elem_1(col2))

I would like to create an index on col2[1] but it's syntactically
supported. I found a way to circumvent the limitation by doing the
following:

scott=> create function proj_elem_1(integer[]) returns integer
scott-> as $$
scott$> begin
scott$> return($1[1]);
scott$> end;
scott$> $$ language plpgsql immutable;
CREATE FUNCTION
scott=> create index ind_t_proj on t(proj_elem_1(col2));
CREATE INDEX

Is there anything else, more elegant than creating a specialized function?


--
http://mgogala.byethost5.com

On 2009-11-25, Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:

try this:

create index ind_t_proj on t(( col2[2] ));

Mladen Gogala wrote:
It *is* syntactically supported. That was a Freudian lapse.

CREATE INDEX ind_t_proj ON t ((col2[1]));

Look at the syntax diagram for CREATE INDEX, there are extra
parentheses around the "expression".

The documentation says:
The key field(s) for the index are specified as column names,
or alternatively as expressions written in parentheses.

I realize that this can easily be misinterpreted, but note
the lack of comma after "expressions".

Yours,
Laurenz Albe

On Thu, 26 Nov 2009 11:08:16 +0100, Laurenz Albe wrote:

Laurenz, I owe you a beer, if you ever come over to NYC:

scott=> CREATE INDEX ind_t_proj ON t ((col2[1]));
CREATE INDEX
scott=> \d t
Table "scott.t"
Column | Type | Modifiers
--------+-----------------------+-----------
col1 | character varying(10) |
col2 | integer[] |
Indexes:
"ind_t_proj" btree ((col2[1]))


Thanks again! I am getting more thrilled by PostgreSQL, the more I study
it.


--
http://mgogala.byethost5.com