Set the language of every search field

How to do it

In PostgreSQL, name the configuration in every to_tsvector and to_tsquery call, such as 'english' or 'swedish'. An expression index on to_tsvector serves only queries that use the same configuration name. Store a language column per row when content mixes languages. The unaccent dictionary makes "Hôtel" match "hotel". For typos, build a word list with ts_stat and match misspelled terms against it with pg_trgm similarity. In MySQL, natural language mode drops stopwords, so very common words never match. In SQLite FTS5, the porter and unicode61 tokenizers handle word endings and accents. Sorting text for people is in Sort and compare text by locale.

Examples

SQL example

-- PostgreSQL: accent-insensitive French search
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE TEXT SEARCH CONFIGURATION shop_fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION shop_fr
  ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

-- PostgreSQL: suggest a spelling from the words in the catalogue
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE product_words AS
  SELECT word FROM ts_stat('SELECT to_tsvector(''simple'', description) FROM products');
CREATE INDEX product_words_trgm ON product_words USING GIN (word gin_trgm_ops);
SELECT word FROM product_words WHERE word % $1 ORDER BY similarity(word, $1) DESC LIMIT 3;

SQL example 2

-- SQLite: word endings and accents in FTS5
CREATE VIRTUAL TABLE product_search USING fts5(
  name, description, tokenize = 'porter unicode61 remove_diacritics 2');

How to check

A test file holds at least 20 word pairs per supported language, such as "running" and "run", "Hôtel" and "hotel", or "recieve" and "receive". Searching each word of a pair returns the same record in the top 10.

Sources

• PostgreSQL text search tables and indexes
• PostgreSQL unaccent module
• PostgreSQL pg_trgm module

Related rules

• Sort and compare text by locale in Website