4 ms·
It was added in 11, but I want to mention again how great of an addition the websearch_to_tsquery() function was. You can now get a respectable search system im
by Deimorz 7y ago
It was added in 11, but I want to mention again how great of an addition the websearch_to_tsquery() function was. You can now get a respectable search system immediately in PostgreSQL without having to implement any sort of query-parsing or special data indexing yourself. All you need to do is add a column/index for fulltext search, which you can even do now using the generated columns added in 12 (instead of a trigger).
Then just pass user-entered queries through websearch_to_tsquery() and have access to multiple search capabilities easily. It supports searching for alternatives with "or", phrases in quotes, and excluding terms with a minus sign, which is all syntax that a lot of people are already used to. For example it can handle a query like:
blizzard or overwatch -"hong kong"
That would find anything with "blizzard" or "overwatch" in it, but exclude anything with "hong kong" (if you wanted to avoid results about that recent controversy).
It's really useful, and makes it so that you can add reasonable search functionality with almost no work at all.
- ComputerGuru 7y agoThat is really cool and definitely a massive timesaver (I've written my own primitive query NLP for SQLite fstext indices and can definitely appreciate this feature!) That said, (and only because your example reminded me) I absolutely hate the NLP `or` operator. I feel like any language to offer an `or` operator needs to require the simultaneous usage of a grouping operator (parentheses, etc.) to clarify the order of operations. A space is an implicit `and` so I never know if the `or` is working on directly adjacent keywords or the query as a whole.