Postgres Fulltext Search IContains not work

[sebasti...@gmail.com]

I have in my Postgres Database a product with following tupple:

(description_de='Elegante Bluse',title_de='Elegante Bluse,slug='elegante-bluse')

Not i have a field for a fulltextsearch over this table. I try to filtering by:

When i now search after "Elegante" it works perfect but when i search after "bluse" no result.

Here are query with searchtext "Bluse":

SELECT description_de,title_de,slug, to_tsvector(german::regconfig, COALESCE("catalogue_product"."title_de", ) || ' ' || COALESCE("catalogue_product"."description_de", ) || ' ' || COALESCE("catalogue_product"."slug", )) AS "search" FROM "catalogue_product" WHERE UPPER(to_tsvector(german::regconfig, COALESCE("catalogue_product"."title_de", ) || ' ' || COALESCE("catalogue_product"."description_de", ) || ' ' || COALESCE("catalogue_product"."slug", ))::text) LIKE UPPER(%Bluse%) ORDER BY "catalogue_product"."date_created" DESC

I have no idea whats happen here. Have someone a idea?

Regards

[jian he]

full text search  link: https://www.postgresql.org/docs/current/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH

LIKE operator link: https://www.postgresql.org/docs/current/functions-matching.html

there are not the same!

demo:

create table test(description_de text,title_de text,slug text);
insert into test values('Elegante Bluse','Elegante Bluse','elegante-bluse');

select *,
        (to_tsvector('german'::regconfig
        ,COALESCE("test"."title_de",' ') || COALESCE("test"."description_de",' ')  || COALESCE("test"."slug", ' '))::text)                
        @@ to_tsquery('german'::regconfig, 'bluse') as bluse_check
        ,to_tsvector('german'::regconfig
            ,COALESCE("test"."title_de",' ') || COALESCE("test"."description_de",' ')|| COALESCE("test"."slug", ' '))
        @@to_tsquery('german'::regconfig, 'Elegante') as Elegante
FROM "test";

---you should use concat_ws. see coalesce and concat_ws difference

with cte as(
    select
        COALESCE("test"."title_de",' ') || COALESCE("test"."description_de",' ')|| COALESCE("test"."slug", ' ') as x
        ,concat_ws(' ',"test"."title_de","test"."description_de","test"."slug") as y
    FROM "test")
select x = y, x,y
    ,to_tsvector('german'::regconfig,x) @@to_tsquery('german'::regconfig, 'Elegante')
    ,to_tsvector('german'::regconfig,y) @@to_tsquery('german'::regconfig, 'Elegante')
from cte;