Full-Text Search in SQL Server and PostgreSQL

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.

• CONTAINS predicate allows to search across the selected columns (or entire row) for a specific word or phrase.
• FORMSOF clause is used by SQL Server for morphological kind of search, i.e. to match different word forms of the terms.

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.

Have questions? Contact us