Formations — 1 037 leçons en français

Accueil › SQL & bases de données › Texte, recherche et collations

Texte, recherche et collations

Types et fonctionsintermediaire

Chercher du texte en SQL couvre trois besoins très différents, avec trois outils.

Préfixe — indexable directement :

CREATE INDEX ON produit (nom);
WHERE nom LIKE 'Clavier%'          -- ✓ index utilisable

Sous-chaîne — il faut un index trigramme :

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX produit_nom_trgm ON produit USING gin (nom gin_trgm_ops);

WHERE nom ILIKE '%mécanique%'      -- ✓ avec l'index trigramme

Recherche par mots — plein texte, avec lemmatisation :

CREATE INDEX produit_fts ON produit
USING gin (to_tsvector('french', nom || ' ' || COALESCE(description, '')));

WHERE to_tsvector('french', nom || ' ' || COALESCE(description, ''))
      @@ plainto_tsquery('french', 'clavier mécanique');

Le plein texte trouve « claviers mécaniques » à partir de « clavier mécanique » : il normalise selon la langue, là où LIKE compare des caractères.

Pour la casse et les accents, la solution générale est l'index sur expression :

CREATE INDEX ON client (lower(nom));
WHERE lower(nom) = lower(:saisie);   -- le prédicat doit être écrit à l'identique

Les termes à connaître

LIKE / ILIKE

Recherche par motif : % remplace n'importe quelle suite, _ un caractère. ILIKE est la variante insensible à la casse, spécifique à PostgreSQL.

Index trigramme (pg_trgm)

Extension qui découpe le texte en séquences de trois caractères. Avec un index GIN ou GiST, elle rend efficace un LIKE '%motif%' — sinon impossible à indexer.

tsvector / tsquery

Types de la recherche plein texte : tsvector est le document découpé en lexèmes normalisés, tsquery la requête. L'opérateur @@ teste la correspondance.

Lemmatisation (*stemming*)

Réduction d'un mot à sa racine selon la langue : « claviers », « clavier » donnent le même lexème. C'est ce qui distingue la recherche plein texte d'un simple LIKE.

Collation

Les règles de comparaison et de tri du texte, dépendantes de la langue : en français, « é » se classe près de « e ». Elle est fixée à la création de la base et influence ORDER BY comme les index.

unaccent

Extension qui supprime les accents : « crème » devient « creme ». Combinée à lower() dans un index sur expression, elle permet une recherche tolérante.

Lire l'explication détaillée et faire le quiz →

Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.