{"id":7873,"date":"2022-09-29T00:00:00","date_gmt":"2022-09-28T22:00:00","guid":{"rendered":"https:\/\/blog.ouidou.fr\/?p=7873"},"modified":"2024-02-02T16:00:12","modified_gmt":"2024-02-02T15:00:12","slug":"recherche-plein-texte-avec-postgresql","status":"publish","type":"post","link":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql","title":{"rendered":"Recherche plein texte avec PostgreSQL"},"content":{"rendered":"<p><em>Article \u00e9crit par Fran\u00e7ois Vantomme<\/em><\/p>\n<p>J\u2019ai r\u00e9cemment eu l\u2019opportunit\u00e9 de travailler pour un client qui souhaitait mettre en place une recherche plus pertinente sur son logiciel. L\u2019occasion r\u00eav\u00e9e de regarder du c\u00f4t\u00e9 de la recherche plein texte (<em>full-text<\/em>) propos\u00e9e nativement par <a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL<\/a>&nbsp;!<\/p>\n<h2 id=\"rvlez-votre-meilleur-profil\">R\u00e9v\u00e9lez votre meilleur profil<\/h2>\n<p>La recherche \u00e9tait effectu\u00e9e sur des profils&nbsp;: un intitul\u00e9, une description, rien de bien exotique.<\/p>\n<p>Historiquement la recherche de profil se faisait tr\u00e8s sommairement sur la base de mots-cl\u00e9s et remontait des r\u00e9sultats peu pertinents. On recherchait, via Ransack, le terme exact faisant tout ou partie d\u2019un mot, dans l\u2019intitul\u00e9 et la description des profils.<\/p>\n<p>Par ailleurs, les mots-cl\u00e9s n\u2019\u00e9taient utilis\u00e9s que pour filtrer les r\u00e9sultats, le tri, lui, \u00e9tait effectu\u00e9 selon le crit\u00e8re de tri choisi (par d\u00e9faut&nbsp;: la date de publication, les plus r\u00e9cents en premier).<\/p>\n<p>Exemple&nbsp;: une recherche avec le mot cl\u00e9 \u00ab&nbsp;app&nbsp;\u00bb fera ressortir les profils dans lesquels figure le mot <code>app<\/code>, mais aussi <code>application<\/code>, <code>app\u00e9tit<\/code> ou encore <code>rapport<\/code>.<\/p>\n<h2 id=\"recherche-plein-texte\">Recherche plein texte sur PostgreSQL<\/h2>\n<p>On comprend d\u00e8s lors que cela manque de pr\u00e9cision et que la pertinence n\u2019est pas au rendez-vous. C\u2019est l\u00e0 que PostgreSQL entre en jeu, et notamment ce que l\u2019on nomme la recherche plein texte. Voici ce que nous en dit la documentation&nbsp;:<\/p>\n<blockquote><p>La recherche plein texte (ou plus simplement la recherche de texte) permet de s\u00e9lectionner des <strong>documents<\/strong> en langage naturel qui satisfont une <strong>requ\u00eate<\/strong> et, en option, de les trier par int\u00e9r\u00eat suivant cette requ\u00eate. Le type le plus fr\u00e9quent de recherche concerne la r\u00e9cup\u00e9ration de tous les documents contenant les <strong>termes de recherche<\/strong> indiqu\u00e9s et de les renvoyer dans un ordre d\u00e9pendant de leur <strong>similarit\u00e9<\/strong> par rapport \u00e0 la requ\u00eate.<\/p>\n<p>\u2014 Chapitre 12. Recherche plein texte<\/p><\/blockquote>\n<p>Il va donc nous \u00eatre possible de formuler une requ\u00eate SQL, un peu velue certes, qui nous permettra d\u2019exprimer ce qui pour nous est digne d\u2019int\u00e9r\u00eat.<\/p>\n<h3 id=\"un-peu-de-vocabulaire\">Un peu de vocabulaire<\/h3>\n<p>\u00c0 ce niveau, il est important de s\u2019arr\u00eater quelques instants sur le vocabulaire pour comprendre de quoi nous parlons exactement.<\/p>\n<p>Un <strong>document<\/strong> est l\u2019unit\u00e9 de recherche, c\u2019est-\u00e0-dire ce sur quoi nous souhaitons effectuer notre recherche. Cela peut \u00eatre un simple champ d\u2019une table de la base de donn\u00e9es, ou la concat\u00e9nation de plusieurs champs, \u00e9ventuellement issus de plusieurs tables. Dans notre exemple, ce qui nous int\u00e9resse c\u2019est l\u2019intitul\u00e9 et la description des profils.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"p\">(<\/span><span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"nv\">\"profiles\"<\/span><span class=\"p\">.<\/span><span class=\"nv\">\"label\"<\/span><span class=\"p\">::<\/span><span class=\"nb\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">)<\/span> <span class=\"o\">||<\/span> <span class=\"n\">coalesce<\/span><span class=\"p\">(<\/span><span class=\"nv\">\"profiles\"<\/span><span class=\"p\">.<\/span><span class=\"nv\">\"description\"<\/span><span class=\"p\">::<\/span><span class=\"nb\">text<\/span><span class=\"p\">,<\/span> <span class=\"s1\">''<\/span><span class=\"p\">))<\/span> <span class=\"k\">AS<\/span> <span class=\"n\">document<\/span> <span class=\"k\">FROM<\/span> <span class=\"nv\">\"profiles\"<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>On utilise ici <code>coalesce()<\/code> pour \u00e9viter de manipuler <code>NULL<\/code> lors de la concat\u00e9nation, ce qui conduirait \u00e0 un r\u00e9sultat nul pour l\u2019ensemble du document.<\/p>\n<p>Une <strong>requ\u00eate<\/strong> (<code>tsquery<\/code>) se fait sur un document (<code>tsvector<\/code>) \u00e0 l\u2019aide de l\u2019op\u00e9rateur <code>@@<\/code>.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'Portez ce vieux whisky au juge blond qui fume'<\/span><span class=\"p\">)<\/span> <span class=\"o\">@@<\/span> <span class=\"n\">to_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'vieux &amp; juge'<\/span><span class=\"p\">)<\/span> <span class=\"o\">+<\/span><span class=\"c1\">----------+<\/span> <span class=\"o\">|<\/span> <span class=\"o\">?<\/span><span class=\"k\">column<\/span><span class=\"o\">?<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">----------|<\/span> <span class=\"o\">|<\/span> <span class=\"k\">True<\/span>     <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">----------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Remarquez que l\u2019on utilise ici <code>to_tsvector()<\/code> et <code>to_tsquery()<\/code>. En effet, nous ne manipulons pas de simples textes. Un <code>tsquery<\/code> contient des termes de recherche qui doivent d\u00e9j\u00e0 \u00eatre des lex\u00e8mes normalis\u00e9s, et peut combiner plusieurs termes en utilisant les op\u00e9rateurs AND, OR, NOT et FOLLOWED BY.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"n\">to_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'vieux &amp; whisky'<\/span><span class=\"p\">)<\/span> <span class=\"o\">+<\/span><span class=\"c1\">--------------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">to_tsquery<\/span>         <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">--------------------|<\/span> <span class=\"o\">|<\/span> <span class=\"s1\">'vieux'<\/span> <span class=\"o\">&amp;<\/span> <span class=\"s1\">'whiski'<\/span> <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">--------------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Notez le mot <code>whisky<\/code> remplac\u00e9 par le lex\u00e8me <code>whiski<\/code>.<\/p>\n<p>Pour pouvoir faire usage de ce <code>tsquery<\/code>, notre document lui sera pr\u00e9sent\u00e9 sous la forme d\u2019un <code>tsvector<\/code>, c\u2019est-\u00e0-dire une version pr\u00e9-trait\u00e9e et compacte de celui-ci.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'Portez ce vieux whisky au juge blond qui fume'<\/span><span class=\"p\">)<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------------------------------------------------------------------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">to_tsvector<\/span>                                                                       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-----------------------------------------------------------------------------------|<\/span> <span class=\"o\">|<\/span> <span class=\"s1\">'au'<\/span><span class=\"p\">:<\/span><span class=\"mi\">5<\/span> <span class=\"s1\">'blond'<\/span><span class=\"p\">:<\/span><span class=\"mi\">7<\/span> <span class=\"s1\">'ce'<\/span><span class=\"p\">:<\/span><span class=\"mi\">2<\/span> <span class=\"s1\">'fume'<\/span><span class=\"p\">:<\/span><span class=\"mi\">9<\/span> <span class=\"s1\">'juge'<\/span><span class=\"p\">:<\/span><span class=\"mi\">6<\/span> <span class=\"s1\">'portez'<\/span><span class=\"p\">:<\/span><span class=\"mi\">1<\/span> <span class=\"s1\">'qui'<\/span><span class=\"p\">:<\/span><span class=\"mi\">8<\/span> <span class=\"s1\">'vieux'<\/span><span class=\"p\">:<\/span><span class=\"mi\">3<\/span> <span class=\"s1\">'whiski'<\/span><span class=\"p\">:<\/span><span class=\"mi\">4<\/span> <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------------------------------------------------------------------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Notre document est ici d\u00e9coup\u00e9 en lex\u00e8mes pr\u00e9sent\u00e9s dans l\u2019ordre alphab\u00e9tique et suivis des indices auxquels on les retrouve dans le document. Pourtant, ces lex\u00e8mes n\u2019ont pas l\u2019air si normalis\u00e9s que \u00e7a\u2026 c\u2019est parce que la recherche plein texte se base sur une configuration qui par d\u00e9faut consid\u00e8re qu\u2019il s\u2019agit d\u2019un document en anglais. Sans entrer trop vite dans le d\u00e9tail de la configuration, sachez qu\u2019on peut pr\u00e9ciser un argument suppl\u00e9mentaire \u00e0 nos fonctions <code>to_tsquery()<\/code> et <code>to_tsvector()<\/code>, voyez&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"c1\">-- to_tsvector([ config regconfig, ] document text) returns tsvector<\/span>  <span class=\"k\">SELECT<\/span> <span class=\"n\">to_tsvector<\/span><span class=\"p\">(<\/span><span class=\"s1\">'french'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">'Portez ce vieux whisky au juge blond qui fume'<\/span><span class=\"p\">)<\/span> <span class=\"o\">+<\/span><span class=\"c1\">---------------------------------------------------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">to_tsvector<\/span>                                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">---------------------------------------------------------|<\/span> <span class=\"o\">|<\/span> <span class=\"s1\">'blond'<\/span><span class=\"p\">:<\/span><span class=\"mi\">7<\/span> <span class=\"s1\">'fum'<\/span><span class=\"p\">:<\/span><span class=\"mi\">9<\/span> <span class=\"s1\">'jug'<\/span><span class=\"p\">:<\/span><span class=\"mi\">6<\/span> <span class=\"s1\">'port'<\/span><span class=\"p\">:<\/span><span class=\"mi\">1<\/span> <span class=\"s1\">'vieux'<\/span><span class=\"p\">:<\/span><span class=\"mi\">3<\/span> <span class=\"s1\">'whisky'<\/span><span class=\"p\">:<\/span><span class=\"mi\">4<\/span> <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">---------------------------------------------------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>On comprend \u00e0 pr\u00e9sent que l\u2019op\u00e9rateur <code>@@<\/code> cherchera dans ce <code>tsvector<\/code> la pr\u00e9sence (ou l\u2019absence) de certains lex\u00e8mes d\u00e9crits par notre <code>tsquery<\/code>. L\u2019int\u00e9r\u00eat de passer par des lex\u00e8mes normalis\u00e9s est de pouvoir d\u00e9couvrir les diff\u00e9rentes formes d\u2019un m\u00eame mot sans avoir \u00e0 toutes les pr\u00e9ciser.<\/p>\n<p>Les plus attentifs d\u2019entre vous auront remarqu\u00e9 la disparition des termes \u00ab&nbsp;ce&nbsp;\u00bb, \u00ab&nbsp;au&nbsp;\u00bb et \u00ab&nbsp;qui&nbsp;\u00bb. Il s\u2019agit l\u00e0 d\u2019un des effets de la configuration choisie qui ignore tout simplement certains mots jug\u00e9s trop g\u00e9n\u00e9riques et non pertinents.<\/p>\n<h2 id=\"une-recherche-aux-petits-oignons\">Une recherche aux petits oignons<\/h2>\n<p>Mais alors, comment fonctionne cette configuration&nbsp;?<\/p>\n<blockquote><p>En interne, la fonction to_tsvector appelle un <strong>analyseur<\/strong> qui casse le texte en <strong>jetons<\/strong> et affecte un <strong>type<\/strong> \u00e0 chaque jeton. Pour chaque jeton, une liste de <strong>dictionnaires<\/strong> est consult\u00e9e, liste pouvant varier suivant le type de jeton. Le premier dictionnaire qui reconna\u00eet le jeton \u00e9met un ou plusieurs <strong>lex\u00e8mes<\/strong> pour repr\u00e9senter le jeton. Le choix de l\u2019analyseur, des dictionnaires et des types de jetons \u00e0 indexer est d\u00e9termin\u00e9 par la configuration de recherche plein texte s\u00e9lectionn\u00e9e. Il est possible d\u2019avoir plusieurs configurations pour la m\u00eame base, et des configurations pr\u00e9d\u00e9finies sont disponibles pour diff\u00e9rentes langues.<\/p>\n<p>\u2014 12.3. Contr\u00f4ler la recherche plein texte<\/p><\/blockquote>\n<p>Ainsi, il nous est possible de choisir une configuration pr\u00e9existante, comme <code>french<\/code> dans l\u2019exemple pr\u00e9c\u00e9dent, mais aussi d\u2019\u00e9laborer une configuration sp\u00e9cialement adapt\u00e9e \u00e0 nos besoins. Et cela tombe bien, car nous en aurons justement besoin&nbsp;! Prenons un exemple.<\/p>\n<h3 id=\"cas-particulier-c-c-net\">Cas particulier&nbsp;: C++, C#, .net<\/h3>\n<p>La recherche par dictionnaire, lorsque celle-ci est effectu\u00e9e \u00e0 l\u2019aide de l\u2019une des configurations mises \u00e0 notre disposition, ignore un certain nombre de symboles (espaces, ponctuation) et les mots jug\u00e9s non pertinents (<em>stopwords<\/em>&nbsp;; particules, mots de liaison). Or, certains langages de programmation, qui peuvent tr\u00e8s bien faire l\u2019objet d\u2019une recherche, contiennent l\u2019un ou l\u2019autre, voire les deux&nbsp;!<\/p>\n<p>Pour palier cela, il nous faut constituer un <strong>th\u00e9saurus<\/strong> personnalis\u00e9, le porter \u00e0 la connaissance de PostgreSQL, le lier \u00e0 un dictionnaire lui aussi personnalis\u00e9, car il ne devra pas discriminer les <em>stopwords<\/em>, et enfin alt\u00e9rer la configuration du fran\u00e7ais pour les caract\u00e8res ASCII et les symboles.<\/p>\n<p>Reprenons. Les dictionnaires sont utilis\u00e9s pour \u00e9liminer les mots qui ne devraient pas \u00eatre consid\u00e9r\u00e9s dans une recherche et pour normaliser des mots qui peuvent prendre des formes diverses. Il existe diff\u00e9rents types de dictionnaires&nbsp;:<\/p>\n<ol>\n<li>Termes courants (<em>stopwords<\/em>)<\/li>\n<li>Dictionnaire simple<\/li>\n<li>Dictionnaire des synonymes<\/li>\n<li>Dictionnaire th\u00e9saurus<\/li>\n<li>Dictionnaire Ispell<\/li>\n<li>Dictionnaire Snowball<\/li>\n<\/ol>\n<p>Une configuration d\u00e9finira ainsi la correspondance entre un type de jeton et un ou plusieurs dictionnaires. En ce qui nous concerne, nous avons besoin d\u2019un th\u00e9saurus pour pouvoir associer un lex\u00e8me \u00e0 un ensemble de jetons.<\/p>\n<p>Notre th\u00e9saurus devra se trouver dans <code>\/usr\/local\/share\/postgresql\/tsearch_data\/<\/code> et nous le nommerons <code>prog_thesaurus.ths<\/code>. Sa syntaxe est plut\u00f4t transparente, voyez vous-m\u00eame&nbsp;:<\/p>\n<pre><code>a + : aplus a # : asharp c - - : cminusminus c + + : cplusplus c\/c + + : cplusplus c # : csharp . net : dotnet f # : fsharp f * : fstar j + + : jplusplus j # : jsharp m # : msharp q # : qsharp r + + : rplusplus xbase + + : xbaseplusplus x + + : xplusplus x # : xsharp z + + : zplusplus <\/code><\/pre>\n<p>Il nous faut maintenant instruire PostgreSQL de l\u2019existence de ce th\u00e9saurus. Mais nous allons faire face \u00e0 un petit souci&nbsp;: certains des jetons utilis\u00e9s dans notre th\u00e9saurus sont des <em>stopwords<\/em>&nbsp;! Pour que notre th\u00e9saurus puisse les prendre en consid\u00e9ration, il faut que ceux-ci ne soient pas ignor\u00e9s par le dictionnaire de base. Ce dictionnaire (Snowball) est bas\u00e9 sur un algorithme de <em>stemming<\/em> qui sait comment r\u00e9duire les variantes standard d\u2019un mot vers une base, ou <em>stem<\/em>, en rapport avec la langue.<\/p>\n<p>Observons tout d\u2019abord le comportement actuel.<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">ts_debug<\/span><span class=\"p\">(<\/span><span class=\"s1\">'french'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">'c++'<\/span><span class=\"p\">);<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------+-----------------+-------+---------------+-------------+---------+<\/span> <span class=\"o\">|<\/span> <span class=\"k\">alias<\/span>     <span class=\"o\">|<\/span> <span class=\"n\">description<\/span>     <span class=\"o\">|<\/span> <span class=\"n\">token<\/span> <span class=\"o\">|<\/span> <span class=\"n\">dictionaries<\/span>  <span class=\"o\">|<\/span> <span class=\"k\">dictionary<\/span>  <span class=\"o\">|<\/span> <span class=\"n\">lexemes<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-----------+-----------------+-------+---------------+-------------+---------|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">asciiword<\/span> <span class=\"o\">|<\/span> <span class=\"n\">Word<\/span><span class=\"p\">,<\/span> <span class=\"k\">all<\/span> <span class=\"n\">ASCII<\/span> <span class=\"o\">|<\/span> <span class=\"k\">c<\/span>     <span class=\"o\">|<\/span> <span class=\"err\">{<\/span><span class=\"n\">french_stem<\/span><span class=\"err\">}<\/span> <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span> <span class=\"o\">|<\/span> <span class=\"p\">[]<\/span>      <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">blank<\/span>     <span class=\"o\">|<\/span> <span class=\"k\">Space<\/span> <span class=\"n\">symbols<\/span>   <span class=\"o\">|<\/span> <span class=\"o\">+<\/span>     <span class=\"o\">|<\/span> <span class=\"err\">{}<\/span>            <span class=\"o\">|<\/span> <span class=\"o\">&lt;<\/span><span class=\"k\">null<\/span><span class=\"o\">&gt;<\/span>      <span class=\"o\">|<\/span> <span class=\"o\">&lt;<\/span><span class=\"k\">null<\/span><span class=\"o\">&gt;<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">blank<\/span>     <span class=\"o\">|<\/span> <span class=\"k\">Space<\/span> <span class=\"n\">symbols<\/span>   <span class=\"o\">|<\/span> <span class=\"o\">+<\/span>     <span class=\"o\">|<\/span> <span class=\"err\">{}<\/span>            <span class=\"o\">|<\/span> <span class=\"o\">&lt;<\/span><span class=\"k\">null<\/span><span class=\"o\">&gt;<\/span>      <span class=\"o\">|<\/span> <span class=\"o\">&lt;<\/span><span class=\"k\">null<\/span><span class=\"o\">&gt;<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------+-----------------+-------+---------------+-------------+---------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">plainto_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'french'<\/span><span class=\"p\">,<\/span><span class=\"s1\">'c++'<\/span><span class=\"p\">)<\/span> <span class=\"n\">NOTICE<\/span><span class=\"p\">:<\/span>  <span class=\"nb\">text<\/span><span class=\"o\">-<\/span><span class=\"k\">search<\/span> <span class=\"n\">query<\/span> <span class=\"k\">contains<\/span> <span class=\"k\">only<\/span> <span class=\"n\">stop<\/span> <span class=\"n\">words<\/span> <span class=\"k\">or<\/span> <span class=\"n\">does<\/span> <span class=\"k\">not<\/span> <span class=\"n\">contain<\/span> <span class=\"n\">lexemes<\/span><span class=\"p\">,<\/span> <span class=\"n\">ignored<\/span>  <span class=\"o\">+<\/span><span class=\"c1\">-----------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">plainto_tsquery<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-----------------|<\/span> <span class=\"o\">|<\/span>                 <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Effectivement, nous nous trouvons l\u00e0 dans une situation cocasse o\u00f9 l\u2019ensemble des jetons de notre requ\u00eate sont ignor\u00e9s&nbsp;: le premier \u00e9tant un <em>stopword<\/em>, les suivants des symboles.<\/p>\n<p>Cr\u00e9ons donc un nouveau dictionnaire Snowball sans <em>stopwords<\/em>&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">CREATE<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"k\">DICTIONARY<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"p\">(<\/span>     <span class=\"k\">TEMPLATE<\/span> <span class=\"o\">=<\/span> <span class=\"n\">pg_catalog<\/span><span class=\"p\">.<\/span><span class=\"n\">snowball<\/span><span class=\"p\">,<\/span>     <span class=\"k\">LANGUAGE<\/span> <span class=\"o\">=<\/span> <span class=\"s1\">'french'<\/span> <span class=\"p\">);<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>D\u00e9clarons \u00e0 pr\u00e9sent notre th\u00e9saurus qui s\u2019appuiera sur notre nouveau dictionnaire <code>french_synbioz_stem<\/code>&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">CREATE<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"k\">DICTIONARY<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">prog_thesaurus<\/span> <span class=\"p\">(<\/span>     <span class=\"k\">TEMPLATE<\/span> <span class=\"o\">=<\/span> <span class=\"n\">pg_catalog<\/span><span class=\"p\">.<\/span><span class=\"n\">thesaurus<\/span><span class=\"p\">,<\/span>     <span class=\"n\">DICTFILE<\/span> <span class=\"o\">=<\/span> <span class=\"s1\">'prog_thesaurus'<\/span><span class=\"p\">,<\/span>     <span class=\"k\">DICTIONARY<\/span> <span class=\"o\">=<\/span> <span class=\"s1\">'public.french_synbioz_stem'<\/span> <span class=\"p\">);<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Pour \u00e9viter toute mauvaise surprise, clonons la configuration <code>french<\/code>&nbsp;; c\u2019est cette r\u00e9plique que nous alt\u00e8rerons par la suite&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">CREATE<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"n\">CONFIGURATION<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz<\/span> <span class=\"p\">(<\/span>   <span class=\"k\">COPY<\/span> <span class=\"o\">=<\/span> <span class=\"n\">french<\/span> <span class=\"p\">);<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Voyons quels dictionnaires sont d\u00e9finis par notre configuration&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"n\">dF<\/span><span class=\"o\">+<\/span> <span class=\"n\">french_synbioz<\/span>  <span class=\"nb\">Text<\/span> <span class=\"k\">search<\/span> <span class=\"n\">configuration<\/span> <span class=\"nv\">\"pg_catalog.french_synbioz\"<\/span> <span class=\"n\">Parser<\/span><span class=\"p\">:<\/span> <span class=\"nv\">\"pg_catalog.default\"<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+--------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">Token<\/span>           <span class=\"o\">|<\/span> <span class=\"n\">Dictionaries<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-----------------+--------------|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">asciihword<\/span>      <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">asciiword<\/span>       <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">email<\/span>           <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">file<\/span>            <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"nb\">float<\/span>           <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"k\">host<\/span>            <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword<\/span>           <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword_asciipart<\/span> <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword_numpart<\/span>   <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword_part<\/span>      <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"nb\">int<\/span>             <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">numhword<\/span>        <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">numword<\/span>         <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">sfloat<\/span>          <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">uint<\/span>            <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">url<\/span>             <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">url_path<\/span>        <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"k\">version<\/span>         <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">word<\/span>            <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>  <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+--------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Modifions \u00e0 pr\u00e9sent notre configuration pour y lier les types de jetons de notre choix \u00e0 notre th\u00e9saurus. L\u2019ordre de d\u00e9claration des dictionnaires a ici une importance, on prendra garde \u00e0 positionner notre th\u00e9saurus en t\u00eate de liste&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">ALTER<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"n\">CONFIGURATION<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz<\/span>   <span class=\"k\">ALTER<\/span> <span class=\"n\">MAPPING<\/span> <span class=\"k\">FOR<\/span> <span class=\"n\">asciiword<\/span><span class=\"p\">,<\/span> <span class=\"n\">asciihword<\/span><span class=\"p\">,<\/span> <span class=\"n\">hword_asciipart<\/span><span class=\"p\">,<\/span> <span class=\"n\">word<\/span>   <span class=\"k\">WITH<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span> <span class=\"n\">french_synbioz_stem<\/span><span class=\"p\">;<\/span>  <span class=\"k\">ALTER<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"n\">CONFIGURATION<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz<\/span>   <span class=\"k\">DROP<\/span> <span class=\"n\">MAPPING<\/span> <span class=\"n\">IF<\/span> <span class=\"k\">EXISTS<\/span> <span class=\"k\">FOR<\/span> <span class=\"n\">blank<\/span><span class=\"p\">;<\/span>  <span class=\"k\">ALTER<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"n\">CONFIGURATION<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz<\/span>   <span class=\"k\">ALTER<\/span> <span class=\"n\">MAPPING<\/span> <span class=\"k\">FOR<\/span> <span class=\"n\">file<\/span><span class=\"p\">,<\/span> <span class=\"k\">host<\/span>   <span class=\"k\">WITH<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span> <span class=\"k\">simple<\/span><span class=\"p\">;<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Pourquoi alt\u00e9rer <code>file<\/code>, me direz-vous&nbsp;? Parce que <code>c\/c<\/code> dans \u00ab&nbsp;c\/c++&nbsp;\u00bb est consid\u00e9r\u00e9 comme un jeton de type <code>file<\/code>.<\/p>\n<p>Si l\u2019on observe notre configuration, elle ressemble \u00e0 pr\u00e9sent \u00e0 ceci&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"n\">dF<\/span><span class=\"o\">+<\/span> <span class=\"n\">french_synbioz<\/span>  <span class=\"nb\">Text<\/span> <span class=\"k\">search<\/span> <span class=\"n\">configuration<\/span> <span class=\"nv\">\"public.french_synbioz\"<\/span> <span class=\"n\">Parser<\/span><span class=\"p\">:<\/span> <span class=\"nv\">\"pg_catalog.default\"<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+------------------------------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">Token<\/span>           <span class=\"o\">|<\/span> <span class=\"n\">Dictionaries<\/span>                       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-----------------+------------------------------------|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">asciihword<\/span>      <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">asciiword<\/span>       <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">email<\/span>           <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">file<\/span>            <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"k\">simple<\/span>              <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"nb\">float<\/span>           <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"k\">host<\/span>            <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"k\">simple<\/span>              <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword<\/span>           <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>                        <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword_asciipart<\/span> <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword_numpart<\/span>   <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">hword_part<\/span>      <span class=\"o\">|<\/span> <span class=\"n\">french_stem<\/span>                        <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"nb\">int<\/span>             <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">numhword<\/span>        <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">numword<\/span>         <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">sfloat<\/span>          <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">uint<\/span>            <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">url<\/span>             <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">url_path<\/span>        <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"k\">version<\/span>         <span class=\"o\">|<\/span> <span class=\"k\">simple<\/span>                             <span class=\"o\">|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">word<\/span>            <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+------------------------------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Parfait&nbsp;! Si l\u2019on teste \u00e0 pr\u00e9sent notre nouvelle configuration&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">ts_debug<\/span><span class=\"p\">(<\/span><span class=\"s1\">'french_synbioz'<\/span><span class=\"p\">,<\/span> <span class=\"s1\">'c++'<\/span><span class=\"p\">);<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-------+-------------------+-------+--------------------------------------+----------------+---------------+<\/span> <span class=\"o\">|<\/span> <span class=\"k\">alias<\/span> <span class=\"o\">|<\/span> <span class=\"n\">description<\/span>       <span class=\"o\">|<\/span> <span class=\"n\">token<\/span> <span class=\"o\">|<\/span> <span class=\"n\">dictionaries<\/span>                         <span class=\"o\">|<\/span> <span class=\"k\">dictionary<\/span>     <span class=\"o\">|<\/span> <span class=\"n\">lexemes<\/span>       <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-------+-------------------+-------+--------------------------------------+----------------+---------------|<\/span> <span class=\"o\">|<\/span> <span class=\"n\">word<\/span>  <span class=\"o\">|<\/span> <span class=\"n\">Word<\/span><span class=\"p\">,<\/span> <span class=\"k\">all<\/span> <span class=\"n\">letters<\/span> <span class=\"o\">|<\/span> <span class=\"k\">c<\/span><span class=\"o\">++<\/span>   <span class=\"o\">|<\/span> <span class=\"err\">{<\/span><span class=\"n\">prog_thesaurus<\/span><span class=\"p\">,<\/span><span class=\"n\">french_synbioz_stem<\/span><span class=\"err\">}<\/span> <span class=\"o\">|<\/span> <span class=\"n\">prog_thesaurus<\/span> <span class=\"o\">|<\/span> <span class=\"p\">[<\/span><span class=\"s1\">'cplusplus'<\/span><span class=\"p\">]<\/span> <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-------+-------------------+-------+--------------------------------------+----------------+---------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">SELECT<\/span> <span class=\"o\">*<\/span> <span class=\"k\">FROM<\/span> <span class=\"n\">plainto_tsquery<\/span><span class=\"p\">(<\/span><span class=\"s1\">'french_synbioz'<\/span><span class=\"p\">,<\/span><span class=\"s1\">'c++'<\/span><span class=\"p\">)<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+<\/span> <span class=\"o\">|<\/span> <span class=\"n\">plainto_tsquery<\/span> <span class=\"o\">|<\/span> <span class=\"o\">|<\/span><span class=\"c1\">-----------------|<\/span> <span class=\"o\">|<\/span> <span class=\"s1\">'cplusplus'<\/span>     <span class=\"o\">|<\/span> <span class=\"o\">+<\/span><span class=\"c1\">-----------------+<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Excellent&nbsp;! Nous observons \u00e0 pr\u00e9sent que notre th\u00e9saurus a reconnu \u00ab&nbsp;c++&nbsp;\u00bb comme \u00e9tant un jeton et lui a substitu\u00e9 le lex\u00e8me \u00ab&nbsp;cplusplus&nbsp;\u00bb.<\/p>\n<h2 id=\"stop-ou-encore\">Stop ou encore&nbsp;?<\/h2>\n<p>Je tiens \u00e0 attirer votre attention sur le fait qu\u2019ignorer purement et simplement les <em>stopwords<\/em> n\u2019est peut-\u00eatre pas souhaitable en conditions r\u00e9elles. En effet, les <em>stopwords<\/em> ont leur int\u00e9r\u00eat dans la mesure de pertinence des r\u00e9sultats retourn\u00e9s. Mais rien ne nous emp\u00eache de d\u00e9clarer notre propre dictionnaire de <em>stopwords<\/em> en y excluant ceux qui entrent en conflit avec notre th\u00e9saurus&nbsp;!<\/p>\n<p>Pour cela on peut s\u2019inspirer du dictionnaire <code>french.stop<\/code>.<\/p>\n<div class=\"language-sh highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"nb\">cd<\/span> \/usr\/local\/share\/postgresql\/tsearch_data\/ <span class=\"nb\">cp <\/span>french.stop french_synbioz.stop <span class=\"c\"># modifier french_synbioz.stop<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Il nous suffit alors de pr\u00e9ciser le dictionnaire <em>stopwords<\/em> \u00e0 utiliser&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">CREATE<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"k\">DICTIONARY<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"p\">(<\/span>   <span class=\"k\">TEMPLATE<\/span> <span class=\"o\">=<\/span> <span class=\"n\">pg_catalog<\/span><span class=\"p\">.<\/span><span class=\"n\">snowball<\/span><span class=\"p\">,<\/span>   <span class=\"k\">LANGUAGE<\/span> <span class=\"o\">=<\/span> <span class=\"s1\">'french'<\/span><span class=\"p\">,<\/span>   <span class=\"n\">STOPWORDS<\/span> <span class=\"o\">=<\/span> <span class=\"s1\">'french_synbioz'<\/span> <span class=\"p\">);<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<p>Si vous \u00eates amen\u00e9 \u00e0 mettre \u00e0 jour l\u2019un de vos dictionnaires, pensez bien \u00e0 recharger votre configuration&nbsp;! Pour cela, voici une petite astuce&nbsp;:<\/p>\n<div class=\"language-sql highlighter-rouge\">\n<div class=\"highlight\">\n<pre class=\"highlight\"><code><span class=\"k\">ALTER<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"k\">DICTIONARY<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">prog_thesaurus<\/span> <span class=\"p\">(<\/span> <span class=\"n\">dummy<\/span> <span class=\"p\">);<\/span> <span class=\"k\">ALTER<\/span> <span class=\"nb\">TEXT<\/span> <span class=\"k\">SEARCH<\/span> <span class=\"k\">DICTIONARY<\/span> <span class=\"k\">public<\/span><span class=\"p\">.<\/span><span class=\"n\">french_synbioz_stem<\/span> <span class=\"p\">(<\/span> <span class=\"n\">dummy<\/span> <span class=\"p\">);<\/span> <\/code><\/pre>\n<\/div>\n<\/div>\n<h2 id=\"et-maintenant\">Et maintenant&nbsp;?<\/h2>\n<p>Nous venons de voir les bases de la recherche plein texte et de sa configuration avec PostgreSQL. Cela fait d\u00e9j\u00e0 de nombreuses notions \u00e0 assimiler, et encore, nous n\u2019avons fait que les survoler ! Pour approfondir cela, je vous invite \u00e0 consulter la <a href=\"https:\/\/www.postgresql.org\/docs\/current\/textsearch.html\">documentation officielle<\/a> ou sa <a href=\"https:\/\/docs.postgresql.fr\/current\/textsearch.html\">version fran\u00e7aise<\/a>, riches d\u2019exemples et de d\u00e9tails.<\/p>\n<p>Dans un prochain article, nous aborderons un autre aspect important de la recherche plein texte&nbsp;: la pond\u00e9ration. S\u2019ensuivra un dernier article pour clore cette s\u00e9rie, il mettra l\u2019accent sur l\u2019indexation et <code>pg_search<\/code>, une gem Ruby nous permettant de cr\u00e9er des <em>scopes<\/em> ActiveRecord qui tirent parti de la recherche plein texte de PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>D\u00e9ployez toute la puissance de PostgreSQL avec la recherche plein texte<\/p>\n","protected":false},"author":10,"featured_media":8500,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","inline_featured_image":false,"fifu_image_url":"","fifu_image_alt":"","footnotes":""},"categories":[34],"tags":[],"class_list":["post-7873","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-articles-techniques"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Recherche plein texte avec PostgreSQL - Ouidou<\/title>\n<meta name=\"description\" content=\"Notre expert vous montre comment mettre en place une recherche plein texte propos\u00e9e nativement par PostgreSQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Recherche plein texte avec PostgreSQL - Ouidou\" \/>\n<meta property=\"og:description\" content=\"Notre expert vous montre comment mettre en place une recherche plein texte propos\u00e9e nativement par PostgreSQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql\" \/>\n<meta property=\"og:site_name\" content=\"Ouidou\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-28T22:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-02T15:00:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/01\/PostgreSQL.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1105\" \/>\n\t<meta property=\"og:image:height\" content=\"564\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Sara Lourties\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sara Lourties\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql\"},\"author\":{\"name\":\"Sara Lourties\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#\\\/schema\\\/person\\\/e1d66085fb7c22ede319ce56adc373a4\"},\"headline\":\"Recherche plein texte avec PostgreSQL\",\"datePublished\":\"2022-09-28T22:00:00+00:00\",\"dateModified\":\"2024-02-02T15:00:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql\"},\"wordCount\":1714,\"publisher\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/01\\\/PostgreSQL.png\",\"articleSection\":[\"Articles Techniques\"],\"inLanguage\":\"fr-FR\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql\",\"url\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql\",\"name\":\"Recherche plein texte avec PostgreSQL - Ouidou\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/01\\\/PostgreSQL.png\",\"datePublished\":\"2022-09-28T22:00:00+00:00\",\"dateModified\":\"2024-02-02T15:00:12+00:00\",\"description\":\"Notre expert vous montre comment mettre en place une recherche plein texte propos\u00e9e nativement par PostgreSQL.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#primaryimage\",\"url\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/01\\\/PostgreSQL.png\",\"contentUrl\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/01\\\/PostgreSQL.png\",\"width\":1105,\"height\":564},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/articles-techniques\\\/recherche-plein-texte-avec-postgresql#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Recherche plein texte avec PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/\",\"name\":\"Blog Ouidou\",\"description\":\"Conseil et expertise en technologies\",\"publisher\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#organization\",\"name\":\"Ouidou\",\"url\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/image_2022-04-13_150237378.png\",\"contentUrl\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/image_2022-04-13_150237378.png\",\"width\":180,\"height\":180,\"caption\":\"Ouidou\"},\"image\":{\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/ouidou.fr\\\/blog\\\/#\\\/schema\\\/person\\\/e1d66085fb7c22ede319ce56adc373a4\",\"name\":\"Sara Lourties\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2ef32f9fc1e16168b39587a2ec1dafedc70a9d41fa1b4a930ec7744b3e35ec7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2ef32f9fc1e16168b39587a2ec1dafedc70a9d41fa1b4a930ec7744b3e35ec7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2ef32f9fc1e16168b39587a2ec1dafedc70a9d41fa1b4a930ec7744b3e35ec7?s=96&d=mm&r=g\",\"caption\":\"Sara Lourties\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Recherche plein texte avec PostgreSQL - Ouidou","description":"Notre expert vous montre comment mettre en place une recherche plein texte propos\u00e9e nativement par PostgreSQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql","og_locale":"fr_FR","og_type":"article","og_title":"Recherche plein texte avec PostgreSQL - Ouidou","og_description":"Notre expert vous montre comment mettre en place une recherche plein texte propos\u00e9e nativement par PostgreSQL.","og_url":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql","og_site_name":"Ouidou","article_published_time":"2022-09-28T22:00:00+00:00","article_modified_time":"2024-02-02T15:00:12+00:00","og_image":[{"width":1105,"height":564,"url":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/01\/PostgreSQL.png","type":"image\/png"}],"author":"Sara Lourties","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Sara Lourties","Dur\u00e9e de lecture estim\u00e9e":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#article","isPartOf":{"@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql"},"author":{"name":"Sara Lourties","@id":"https:\/\/ouidou.fr\/blog\/#\/schema\/person\/e1d66085fb7c22ede319ce56adc373a4"},"headline":"Recherche plein texte avec PostgreSQL","datePublished":"2022-09-28T22:00:00+00:00","dateModified":"2024-02-02T15:00:12+00:00","mainEntityOfPage":{"@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql"},"wordCount":1714,"publisher":{"@id":"https:\/\/ouidou.fr\/blog\/#organization"},"image":{"@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#primaryimage"},"thumbnailUrl":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/01\/PostgreSQL.png","articleSection":["Articles Techniques"],"inLanguage":"fr-FR"},{"@type":"WebPage","@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql","url":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql","name":"Recherche plein texte avec PostgreSQL - Ouidou","isPartOf":{"@id":"https:\/\/ouidou.fr\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#primaryimage"},"image":{"@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#primaryimage"},"thumbnailUrl":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/01\/PostgreSQL.png","datePublished":"2022-09-28T22:00:00+00:00","dateModified":"2024-02-02T15:00:12+00:00","description":"Notre expert vous montre comment mettre en place une recherche plein texte propos\u00e9e nativement par PostgreSQL.","breadcrumb":{"@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql"]}]},{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#primaryimage","url":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/01\/PostgreSQL.png","contentUrl":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/01\/PostgreSQL.png","width":1105,"height":564},{"@type":"BreadcrumbList","@id":"https:\/\/ouidou.fr\/blog\/articles-techniques\/recherche-plein-texte-avec-postgresql#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/ouidou.fr\/blog\/"},{"@type":"ListItem","position":2,"name":"Recherche plein texte avec PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/ouidou.fr\/blog\/#website","url":"https:\/\/ouidou.fr\/blog\/","name":"Blog Ouidou","description":"Conseil et expertise en technologies","publisher":{"@id":"https:\/\/ouidou.fr\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ouidou.fr\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/ouidou.fr\/blog\/#organization","name":"Ouidou","url":"https:\/\/ouidou.fr\/blog\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/ouidou.fr\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/04\/image_2022-04-13_150237378.png","contentUrl":"https:\/\/ouidou.fr\/blog\/wp-content\/uploads\/2022\/04\/image_2022-04-13_150237378.png","width":180,"height":180,"caption":"Ouidou"},"image":{"@id":"https:\/\/ouidou.fr\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/ouidou.fr\/blog\/#\/schema\/person\/e1d66085fb7c22ede319ce56adc373a4","name":"Sara Lourties","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/secure.gravatar.com\/avatar\/b2ef32f9fc1e16168b39587a2ec1dafedc70a9d41fa1b4a930ec7744b3e35ec7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b2ef32f9fc1e16168b39587a2ec1dafedc70a9d41fa1b4a930ec7744b3e35ec7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b2ef32f9fc1e16168b39587a2ec1dafedc70a9d41fa1b4a930ec7744b3e35ec7?s=96&d=mm&r=g","caption":"Sara Lourties"}}]}},"_links":{"self":[{"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/posts\/7873","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/comments?post=7873"}],"version-history":[{"count":6,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/posts\/7873\/revisions"}],"predecessor-version":[{"id":12582,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/posts\/7873\/revisions\/12582"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/media\/8500"}],"wp:attachment":[{"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/media?parent=7873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/categories?post=7873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ouidou.fr\/blog\/wp-json\/wp\/v2\/tags?post=7873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}