Indexes on arrays

[Mladen Gogala]

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

[Mladen Gogala]

On Wed, 25 Nov 2009 19:59:10 +0000, Mladen Gogala wrote:

> but it's syntactically
> supported.

It's NOT syntactically supported. Lapsus keyboardi.

--
http://mgogala.byethost5.com

[Jasen Betts]

On 2009-11-25, Mladen Gogala <n...@email.here.invalid> wrote:
> I have a table defined like this:
>
> scott=> \d t
> Table "public.t"
> Column | Type | Modifiers
> --------+----------------------+-----------
> col1 | character varying(3) |
> col2 | integer[] |

try this:

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

[Laurenz Albe]

Mladen Gogala wrote:
> 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:
>

[...]

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

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

[Mladen Gogala]

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