Search configuration and languages in PostgreSQL

Introduction

Search configuration helps the database understand how to find words in different languages. It makes searching faster and more accurate.

You want to search text in a specific language like English or French.

You need to find words ignoring small differences like plurals or accents.

You want to improve search results by using language rules.

You want to add support for a new language in your database search.

You want to customize how words are recognized during search.

Syntax

PostgreSQL

CREATE TEXT SEARCH CONFIGURATION config_name ( COPY = existing_config );

ALTER TEXT SEARCH CONFIGURATION config_name
  ADD MAPPING FOR token_type WITH dictionary_name;

CREATE TEXT SEARCH CONFIGURATION makes a new search setup by copying an existing one.

ALTER TEXT SEARCH CONFIGURATION changes how words are processed by adding dictionaries for certain word types.

Examples

This creates a new search configuration called my_english by copying the built-in English configuration.

PostgreSQL

CREATE TEXT SEARCH CONFIGURATION my_english ( COPY = english );

This changes my_english to use the english_stem dictionary for normal words, helping to find word roots.

PostgreSQL

ALTER TEXT SEARCH CONFIGURATION my_english
  ADD MAPPING FOR asciiword, word WITH english_stem;

This converts the sentence into searchable tokens using the my_english configuration.

PostgreSQL

SELECT to_tsvector('my_english', 'The quick brown foxes jumped');

Sample Program

This example creates a French search configuration, adds stemming to find word roots, and then converts a French sentence into searchable tokens.

PostgreSQL

CREATE TEXT SEARCH CONFIGURATION my_french ( COPY = french );

ALTER TEXT SEARCH CONFIGURATION my_french
  ADD MAPPING FOR asciiword, word WITH french_stem;

SELECT to_tsvector('my_french', 'Les chats sautent rapidement');

Important Notes

Search configurations control how text is broken into words and how those words are normalized.

Languages have different rules, so using the right configuration improves search quality.

You can list available configurations with SELECT * FROM pg_ts_config;

Summary

Search configurations help PostgreSQL understand text in different languages.

You create or copy configurations and add dictionaries for word processing.

Using the right configuration improves search accuracy and speed.