PostgreSQL search all indexes of an array

Question

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!

Ihor Romanchenko · Accepted Answer · 2013-09-26 17:57:30Z

1

Try something like:

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

answered Sep 26, 2013 at 17:57

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MurifoX · Accepted Answer · 2013-09-26 17:58:31Z

1

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

answered Sep 26, 2013 at 17:58

MurifoX

15.1k3 gold badges40 silver badges60 bronze badges

1 Comment

PeterInvincible Over a year ago

Interestingly I get a syntax error for this query. The error message is this: Query failed: ERROR: syntax error at or near "ANY". What could be wrong?

Collectives™ on Stack Overflow

PostgreSQL search all indexes of an array

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related