CODEWITHSUNDEEP
phpsqlarrayspostgresql
PeterInvincible
PeterInvincible

Reputation: 2310

PostgreSQL search all indexes of an array

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!

Upvotes: 1

Views: 127

Answers (2)

MurifoX
MurifoX

Reputation: 15089

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

Upvotes: 1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28531

Try something like:

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

Upvotes: 1

Related Questions