PostgreSQL search all indexes of an array

1
votes

In my table 'products' I have a column named 'keywords'.

It's defined like this:

CREATE TABLE products (
    id            integer,
    keywords      character varying[]
    //rest of the definition ommited for clarity
);

A record in column categories could look something like this:

{music,rock,mp3,nirvana}

I'm trying to implement an ajax search function on my website, where on each keypress a search function gets called.

If the user is writing "m", I'd like it to query the database and retreive the row which's "keywords" column's array contains anything like that.

Here's the query I'm using right now:

SELECT * FROM products WHERE keywords[1] SIMILAR TO $queryString

The problem is that with this query only the first index of keywords[] gets searched.

So from my previous example:

{music,rock,mp3,nirvana}

It only searches the first index, which is "music", it doesn't search the whole array.

How can I query this array to search throught the FULL ARRAY?

Thanks!

2
phpsqlarrayspostgresql

2 Answers

1
votes

Try something like:

SELECT * FROM products WHERE ANY(keywords) SIMILAR TO $queryString
1
votes

According to the documentation on arrays found here http://www.postgresql.org/docs/9.1/static/arrays.html

you can query the array field using ANY or ALL: 

SELECT * FROM products WHERE ANY(keywords) SIMILAR TO $queryString