CODEWITHSUNDEEP
sqlpostgresql
Craig Myles
Craig Myles

Reputation: 5454

Postgres text search on multiple rows

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'), ' ')
)

Upvotes: 1

Views: 447

Answers (1)

David S
David S

Reputation: 13841

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

Upvotes: 1

Related Questions