use a column as an input for to_tsquery

Question

I have a table T1 with only one column having 2000 unique words. There is another table T2 with a column of a word. I want to find the records in T2 were their words matches with one of the words in T1. So imagine T1 looks as following:

word ------ regents sky tree trees avenue

and T2 is like this:

tags | id| usrid --------+----+-------- shifs | 1| @1 trees | 2| @2 sky | 3| @3 regents | 4| @4 regent | 5| @5

and I want to get results as follow:

tags | id| usrid --------+----+-------- trees | 2| @2 sky | 3| @3 regents | 5| @5

I could use the below query to search the tags column from T2 but I want to use the word column from T1 as an input for tsquery.

SELECT * FROM T2 WHERE to_tsvector(tags) @@ to_tsquery('regent');

Laurenz Albe · Accepted Answer · 2018-11-07 13:06:30Z

1

That would be a simple join between the tables:

SELECT t2.* FROM t1 JOIN t2 ON t2.tags = t1.word;

No need for full text search!

edited Nov 7, 2018 at 13:06

answered Nov 7, 2018 at 12:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

GeoBeez Over a year ago

thanks, it worked very well. just there is one problem. in T1 I have 'regents' and in T2 i have 'regent' and 'regents'. I want it only choose regents! but it does both of them.

Laurenz Albe Over a year ago

Then you shouldn't use full text search; see my updated answer.

Laurenz Albe Over a year ago

No, it's actually slower. Depending on the size of your tables, creating and index on one of them may help.

Collectives™ on Stack Overflow

use a column as an input for to_tsquery

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related