Full-Text Search in SQL Server and PostgreSQL
|[SQL Server to Postgres Converter] [About Migration] [How to Control Migration Results]|
Although both SQL Server and PostgreSQL have full-text search capabilities, there are some features that have no equvalents in PostgreSQL. This article explores how to migrate those features from SQL Server to PostgreSQL.
The query below extracts all rows from table "documents" having the word "query" or "queries" in any column:
select * from documents where contains(*, 'formsof(inflectional, "query")')
PostgreSQL provides extended capabilities of full-text search. It uses dictionaries to do linguistic search for multiple derived forms of a word. This approach requires some proprocessing over the data and affected queries. Preprocessed text data is stored as a tsvector data type, and processed queries are stored as the tsquery type. After it is done, PostgreSQL parses text documents into linguistic units known as lexemes, which allows to find all possible derivatives of a word.
Visit the official PostgreSQL site to learn more about dictionaries.