CODEWITHSUNDEEP
postgresql
O. Conor
O. Conor

Reputation: 51

Postgres - difference between to_tsquery, to_tsvector and plainto_tsquery

The Full text search of postgres includes some of these functions to search: plainto_tsquery, to_tsquery and to_tsvector . I don't get the difference between it, the results contain the same words always, but in tsvector it is detached with the number of position of that word.

SELECT plainto_tsquery('simple', 'The & Fat & Rats');

result will be like this: plainto_tsquery: 'fat' & 'rat' to_tsquery: 'fat' & 'rat' to_tsvector: 'fat':2 'rat':3

I have tried longer queries, but i haven't found a bigger difference than that.

I already read the documentation, but I didnt get the difference there either.

I am happy for any help.

Upvotes: 5

Views: 14600

Answers (1)

jjanes
jjanes

Reputation: 44305

"plainto_tsquery" takes a phrase in plain English (or in this case plain "simple"--although your question is not consistent. "simple" does not strip out the word 'the', the way you show, unless you made nonstandard modifications to it) and converts it to a tsquery. Since "&" is punctuation, it gets ignored. But then it adds '&' in between the words, because that is what "plainto_tsquery" does. So those changes are not visible, because you chose a poor example to feed to plainto_tsquery.

"to_tsquery" compiles the query you gave it into the structure used for searching. But then, because you are selecting it rather than using it with a ts query operator, it converts it back to text again so it can display it. It requires that what you feed it already looks mostly like a tsquery (for example, has boolean operators between each word), otherwise it throws an error. Surely you noticed that when you tried longer queries?

"to_tsvector" creates a tsvector. This is not a tsquery, rather it is what the tsquery gets applied to.

Upvotes: 11

Related Questions