0

I have a table, that contains a text-column "title" and an autogenerated column "fts_title", that contains the tsvector-based values for the original values, pre-generated and stored as tsvector:

CREATE TABLE .... AS ... fts_title tsvector generated always as .... stored); 

I can perform a fulltext search easily for certain words with such statements:

SELECT a.id, title FROM article a WHERE a.fts_body @@ plainto_tsquery('english', 'Berlin'); 

But how can I find similar articles that have the same tsvector-based title?

When I do something like this:

SELECT a.id, title FROM article a WHERE a.fts_title @@ (SELECT fts_title FROM article WHERE id = 455854); 

it gives an error message saying:

ERROR: operator does not exist: tsvector @@ tsvector Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I understand that tsvector and tsvector can't get compared this way. Is that even possible?

I also tried ts_rank with two tsvector-columns but this gives a similar error:

function ts_rank(tsvector, tsvector) does not exist

select ts_rank(a.fts_title, b.fts_title) as rank, a.id, title from article a inner join ... article b; 

1 Answer 1

0

Use the actual title in your search:

SELECT a.id, title FROM article a WHERE a.fts_title @@ (SELECT to_tsquery('english', actual_title) FROM article WHERE id = 455854); 

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.