By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Index on array element

P: n/a
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)

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Steve Crawford <sc*******@pinpointresearch.com> writes:
How can I create an index on an array element?


You need 7.4 and an extra layer of parentheses:

create index foodex on foo ((textarray[3]));

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
Steve Crawford <sc*******@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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3

P: n/a
>> How can I create an index on an array element?

You need 7.4 and an extra layer of parentheses:

create index foodex on foo ((textarray[3]));


Sorry, but this isn't obvious to me as arrays in a database are a new
concept for me -- why are the extra layer of parenthesis needed?

--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.
Nov 12 '05 #4

P: n/a
Randolf Richardson <rr@8x.ca> writes:
How can I create an index on an array element?
You need 7.4 and an extra layer of parentheses:

create index foodex on foo ((textarray[3]));

Sorry, but this isn't obvious to me as arrays in a database are a new
concept for me -- why are the extra layer of parenthesis needed?


It's got nothing to do with arrays as such, but is purely a syntactic
restriction: if the indexed item is anything but an unadorned column
name or a function call, CREATE INDEX wants parentheses around it.

This is because the CREATE INDEX syntax also allows for an "operator
class" name in there. Consider

create index foodex on foo (bar ! ops);

Is this supposed to mean indexing the expression "bar ! ops" (infix !
operator) or indexing the expression "bar !" (postfix ! operator) using
the operator class "ops"? No way to tell, so we make you clarify your
intent with parentheses. You can say either

create index foodex on foo ((bar ! ops));
create index foodex on foo ((bar !) ops);

depending which interpretation you want.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #5

P: n/a
[sNip]
It's got nothing to do with arrays as such, but is purely a syntactic
restriction: if the indexed item is anything but an unadorned column
name or a function call, CREATE INDEX wants parentheses around it.

This is because the CREATE INDEX syntax also allows for an "operator
class" name in there. Consider

create index foodex on foo (bar ! ops);

Is this supposed to mean indexing the expression "bar ! ops" (infix !
operator) or indexing the expression "bar !" (postfix ! operator) using
the operator class "ops"? No way to tell, so we make you clarify your
intent with parentheses. You can say either

create index foodex on foo ((bar ! ops));
create index foodex on foo ((bar !) ops);

depending which interpretation you want.


Thanks. Your explanation is very helpful.

--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.