PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Types de données » Types de recherche plein texte

8.11. Types de recherche plein texte

PostgreSQL fournit deux types de données conçus pour supporter la recherche plein texte qui est l'activité de recherche via une collection de documents en langage naturel pour situer ceux qui correspondent le mieux à une requête. Le type tsvector représente un document dans une forme optimisée pour la recherche plein texte alors que le type tsquery représente de façon similaire une requête. Chapitre 12 fournit une explication détaillée de cette capacité et Section 9.13 résume les fonctions et opérateurs en relation.

8.11.1. tsvector

Une valeur tsvector est une liste triée de lexemes distincts, qui sont des mots qui ont été normalisés pour fusionner différentes variantes du même mot apparaissant (voir Chapitre 12 pour plus de détails). Trier et éliminer les duplicats se font automatiquement lors des entrées, comme indiqué dans cet exemple :

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
       

Pour représenter des lexèmes contenant des espaces blancs ou des signes de ponctuation, entourez-les avec des guillemets simples :

SELECT $$the lexeme '    ' contains spaces$$::tsvector;
                 tsvector
-------------------------------------------
 '    ' 'contains' 'lexeme' 'spaces' 'the'
       

(Nous utilisons les valeurs littérales entre guillemets simples dans cet exemple et dans le prochain pour éviter une confusion en ayant à doubler les guillemets à l'intérieur des valeurs littérales.) Les guillemets imbriqués et les antislashs doivent être doublés :

SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
                    tsvector
------------------------------------------------
 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
       

En option, les positions peuvent être attachées aux lexèmes :

SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
                                  tsvector
-------------------------------------------------------------------------------
 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4
       

Une position indique normalement l'emplacement du mot source dans le document. Les informations de position sont utilisables pour avoir un score de proximité. Les valeurs des positions peuvent aller de 1 à 16383 ; les grands nombres sont limités silencieusement à 16383. Les positions dupliquées du même lexème sont rejetées.

Les lexèmes qui ont des positions peuvent aussi avoir un label d'un certain poids. Les labels possibles sont A, B, C ou D. D est la valeur par défaut et n'est du coup pas affiché en sortie :

SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
          tsvector
----------------------------
 'a':1A 'cat':5 'fat':2B,4C
       

Les poids sont typiquement utilisés pour refléter la structure du document en marquant les mots du titre de façon différente des mots du corps. Les fonctions de score de la recherche plein texte peuvent assigner des priorités différentes aux marqueurs de poids différents.

Il est important de comprendre que le type tsvector lui-même ne réalise aucune normalisation de mots ; il suppose que les mots qui lui sont fournis sont normalisés correctement pour l'application. Par exemple,

SELECT 'The Fat Rats'::tsvector;
      tsvector
--------------------
 'Fat' 'Rats' 'The'
       

Pour la plupart des applications de recherche en anglais, les mots ci-dessus seraient considérés comme non normalisés, mais tsvector n'y prête pas attention. Le texte des documents bruts doit habituellement passer via to_tsvector pour normaliser les mots de façon appropriée pour la recherche :

SELECT to_tsvector('english', 'The Fat Rats');
   to_tsvector
-----------------
 'fat':2 'rat':3
       

De nouveau, voir Chapitre 12 pour plus de détails.

8.11.2. tsquery

Une valeur tsquery enregistre les lexèmes qui doivent être recherchés, et peut les combiner en utilisant les opérateurs booléens & (AND), | (OR) et ! (NOT), ainsi que l'opérateur de recherche de phrase <-> (FOLLOWED BY). Il existe aussi une variante de l'opérateur FOLLOWED BY, <N>, où N est une constante entière indiquant la distance maximale entre les deux lexèmes recherchés. <-> est équivalent à <1>.

Les parenthèses peuvent être utilisées pour forcer le regroupement des opérateurs. En l'absence de parenthèses, ! (NOT) est prioritaire, <-> (FOLLOWED BY) suit, et enfin & (AND) et | (OR) sont les moins prioritaires.

Voici quelques exemples :

 SELECT 'fat & rat'::tsquery;
    tsquery
---------------
 'fat' & 'rat'

SELECT 'fat & (rat | cat)'::tsquery;
          tsquery
---------------------------
 'fat' & ( 'rat' | 'cat' )

SELECT 'fat & rat & ! cat'::tsquery;
        tsquery
------------------------
 'fat' & 'rat' & !'cat'
       

En option, les lexèmes dans une tsquery peuvent être labelisés avec une lettre de poids ou plus, ce qui les restreint à une correspondance avec les seuls lexèmes tsvector pour un de ces poids :

SELECT 'fat:ab & cat'::tsquery;
    tsquery
------------------
 'fat':AB & 'cat'
       

Par ailleurs, les lexèmes d'une tsquery peuvent être marqués avec * pour spécifier une correspondance de préfixe :

SELECT 'super:*'::tsquery;
  tsquery
-----------
 'super':*
       

Cette requête fera ressortir tout mot dans un tsvector qui commence par « super ».

Les règles de guillemets pour les lexèmes sont identiques à celles décrites ci-dessus pour les lexèmes de tsvector ; et, comme avec tsvector, toute normalisation requise des mots doit se faire avant de les placer dans le type tsquery. La fonction to_tsquery est convenable pour réaliser une telle normalisation :

SELECT to_tsquery('Fat:ab & Cats');
    to_tsquery
------------------
 'fat':AB & 'cat'
       

Notez que to_tsquery traitera les préfixes de la même façon que les autres mots, ce qui signifie que cette comparaison renvoie true :

SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );
 ?column?
----------
 t
       

parce que postgres devient postgr :

SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
  to_tsvector  | to_tsquery
---------------+------------
 'postgradu':1 | 'postgr':*
       

qui correspondra à la forme native de postgraduate.