Postgres text search on multiple rows

Question

I have a table called 'exclude' that contains hashtags:

-------------
id | tag
-------------
1    #oxford
2    #uk
3    #england
-------------

I have another table called 'post':

-----------------------------------------------
id | tags               | text
1    #oxford #funtimes    Sitting in the sun
2    #oz                  Beach fun
3    #england             Milk was a bad choice
-----------------------------------------------

In order to do a text search on the posts tags I've been running a query like follows:

SELECT * FROM post WHERE to_tsvector(tags) @@ plainto_tsquery('mysearchterm')

However, I now want to be able to exclude all posts where some or all of the tags are in my exclude table. Is there any easy way to do this in SQL/Postgres?

I tried converting the tags row into one column, and using this term within the plainto_tsquery function but it doesn't work (I don't know how to do a text search 'not equal' to either, hence the logic is actual wrong, albeit on the right lines in my mind):

select * from post where to_tsvector(tags) @@ plainto_tsquery(
   select array_to_string(array(select RTRIM(value) from exclude where key = 'tag'), ' ')
)

David S · Accepted Answer · 2012-09-27 04:07:16Z

1

What version of PostgreSQL are you on? And how flexible is your schema design? In other words, can you change it at will? Or is this out of your control?

Two things immediately popped to mind when I read your questions. One is you should be able to use array and the the @> (contains) or <@ (is contains by) operators.

Here is documentation

Second, you might be able to utilize an hstore and do a similar operation. to:

hstore @> hstore

It's not a true hstore, because you are not using a real key=>value pair. But, I guess you could do {tagname}=True or {tagname}=NULL. Might be a bit hackish.

You can see the documentation (for PostgreSQL 9.1) hstore and how to use it here

answered Sep 27, 2012 at 4:07

David S

14.1k13 gold badges60 silver badges97 bronze badges

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

1 Comment

Craig Myles Over a year ago

Legend! Thanks man. I used the concept of arrays and came up with the following which seems to work a treat: SELECT tags FROM post WHERE string_to_array(tags, ' ') && (SELECT array(SELECT RTRIM(value) FROM exclude WHERE key = 'tag')) AND tags != ''. You'll notice my table structure for the tags is actually id, key, value where the key is 'tag' and value is '#oxford' etc

Collectives™ on Stack Overflow

Postgres text search on multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related