Re: Selecting rows by content of array type field - Mailing list pgsql-general

On Thu, Aug 13, 2009 at 04:02:14PM +0300, mito wrote:
> I have table like this:
> CREATE TABLE messages {
>     recepients varchar[],
> };
>
> Want to select by content co array type field recepients:
> SELECT * FROM messages where 'john' ANY (recepients);

not sure if it went missing in the email, but you want an equals in
there, i.e:

  SELECT * FROM messages WHERE 'john' = ANY(recepients);

> If i want to create index on recepients field, is it enough to
> CREATE INDEX messages_recepients_index ON messages (recepients);
> or is there other way how to index this? Expressions?

I think you want a GIN index; have a look at:

  http://www.postgresql.org/docs/current/static/indexes.html

--
  Sam  http://samason.me.uk/