CODEWITHSUNDEEP
postgresql
shuba.ivan
shuba.ivan

Reputation: 4071

How to search hyphenated whole words in PostgreSQL 11.8 in full text search?

I use PostgreSQL 11.8. I have query which return result which I'm not expected. I want to compare whole word with OR condition with key_words from my categories and sun categories. For that I created this query and it works pretty well for some point, when I faced with words with hyphens. And in this case I have wrong bahaviour, my word long matched for long-sleevedt-shirt this is not correct

        SELECT             
        DISTINCT ca.id
            ,ca.category_name            
            ,cc.key_words AS main_keywords
            ,ts_rank_cd(to_tsvector('pg_catalog.swedish',cc.key_words),to_tsquery('pg_catalog.swedish', :main_search_parial_category)) AS  main_runk

            ,cr_main.sub_category_id AS sub_ctegory_id
                ,crsub.key_words AS sub_keywords
                ,ts_rank_cd(to_tsvector('pg_catalog.swedish',crsub.key_words),to_tsquery('pg_catalog.swedish', :sub_main_search)) AS  sub_runk

            FROM category as ca

        INNER JOIN category_relations as cr_ca_main ON cr_ca_main.sub_category_id != ca.id
        INNER JOIN category_configurations as cc ON cc.category_id_id = ca.id

            INNER JOIN category_relations as cr_main ON cr_main.main_category_id = ca.id
            INNER JOIN category_configurations as crsub ON crsub.category_id_id = cr_main.sub_category_id

            WHERE to_tsvector('pg_catalog.swedish',cc.key_words) @@ to_tsquery('pg_catalog.swedish', :main_search_parial_category)

            AND to_tsvector('pg_catalog.swedish',crsub.key_words) @@ to_tsquery('pg_catalog.swedish', :sub_main_search)

        ORDER BY
            ca.id
            ,cc.key_words

                ,cr_main.sub_category_id
                    ,crsub.key_words
id |category_name|main_keywords|main_runk|sub_ctegory_id|sub_keywords|sub_runk
1  |Barn         |Barn,barn    |0.2      |2             |t-shirt,kortärmad,tee,shortsleve,piké,pike,långärmadt-shirt,t-shirt,short-sleeved,tee,shortsleve,piké,girl,long-sleevedt-shirt|0.1

sub_runk = 0.1 looks like long matched for long-sleevedt-shirt key word, but it's not correct I want to whole expression long-sleevedt-shirt, 'long' should not macthed to this key word. I tested it separatly 

    select
                to_tsvector('pg_catalog.swedish','t-shirt,kortärmad,tee,shortsleve,piké,pike,långärmadt-shirt,t-shirt,short-sleeved,tee,shortsleve,piké,girl,long-sleevedt-shirt') 
                @@ to_tsquery('pg_catalog.swedish', 'Millou|Bunny|long|ears|liten|rainbow|apples|fuchsia') as match;

match = t

when I remove long I had f false, this is correct

and faced with the same result, macth equal true by word long, it's not correct should be mathcing if whole expression long-sleevedt-shirt equal for some search part.

when I changed to_tsquery to phraseto_tsquery I faced wth another problem, when I set long-sleevedt-shirt to phraseto_tsquery result should be true, but I faced with false, why or maybe this is wrong approach ? 

select
            to_tsvector('pg_catalog.swedish','t-shirt,kortärmad,tee,shortsleve,piké,pike,långärmadt-shirt,t-shirt,short-sleeved,tee,shortsleve,piké,girl,long-sleevedt-shirt') 
            @@ phraseto_tsquery('pg_catalog.swedish', 'Millou|Bunny|long-sleevedt-shirt|ears|liten|rainbow|apples|fuchsia') as match;

How to set some stric mode or something like that for check only if whole expression match ?

NOTATION

One thing about GIN index, it should be changed(or created new index) to new text search configuration

create index kw_my_swedish_custom_index on category_configurations
using GIN(to_tsvector('my_swedish', key_words))

Upvotes: 2

Views: 676

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247300

The full text parser parses hyphenated words so that the whole word as well as its hyphenated parts become tokens:

SELECT * FROM ts_debug('swedish', 'long-sleevedt-shirt');

      alias      |           description           |        token        |  dictionaries  |  dictionary  |        lexemes        
-----------------+---------------------------------+---------------------+----------------+--------------+-----------------------
 asciihword      | Hyphenated word, all ASCII      | long-sleevedt-shirt | {swedish_stem} | swedish_stem | {long-sleevedt-shirt}
 hword_asciipart | Hyphenated word part, all ASCII | long                | {swedish_stem} | swedish_stem | {long}
 blank           | Space symbols                   | -                   | {}             |              | 
 hword_asciipart | Hyphenated word part, all ASCII | sleevedt            | {swedish_stem} | swedish_stem | {sleeved}
 blank           | Space symbols                   | -                   | {}             |              | 
 hword_asciipart | Hyphenated word part, all ASCII | shirt               | {swedish_stem} | swedish_stem | {shirt}
(6 rows)

One way I can imagine of getting rid of the hword_asciipart tokens is to create a text search configuration that does not process them:

CREATE TEXT SEARCH CONFIGURATION my_swedish (
   COPY = swedish
);
ALTER TEXT SEARCH CONFIGURATION my_swedish
   DROP MAPPING FOR hword_asciipart;
ALTER TEXT SEARCH CONFIGURATION my_swedish
   DROP MAPPING FOR hword_part;

Then use that text search configuration:

SELECT to_tsvector('my_swedish','t-shirt,kortärmad,tee,shortsleve,piké,pike,långärmadt-shirt,t-shirt,short-sleeved,tee,shortsleve,piké,girl,long-sleevedt-shirt')
       @@ to_tsquery('my_swedish', 'Millou|Bunny|long|ears|liten|rainbow|apples|fuchsia');

Upvotes: 3

Related Questions