Re: Full text search prefix matching

On Tue, 16 Dec 2014 14:59:51 +0200
Heikki Rauhala <heikki.rauhala@reaktor.fi> wrote:

Hi Heikki,

There is a typo :

>
> create materialized view name_fulltext as

ERREUR:  erreur de syntaxe sur ou près de « materialized »
LIGNE 1 : create materialized view name_fulltext as

You might want to post the results too, next time; I posted them with a corrected script below:

create table names (name varchar);
insert into names (name) values ('Sofia');

create view name_fulltext as
  select
    name,
    to_tsvector('finnish', name) as
      searchable_index_col
  from names;

select * from name_fulltext;
select to_tsquery('finnish','sof:*');

select 'found sof', name
from name_fulltext
where searchable_index_col@@to_tsquery('finnish','sof:*');

select 'notfound sofi', name
from name_fulltext
where searchable_index_col@@to_tsquery('finnish','sofi:*');

select 'found sofia', name
from name_fulltext
where searchable_index_col@@to_tsquery('finnish','sofia:*');


vv=> select * from name_fulltext;
 name  | searchable_index_col
-------+----------------------
 Sofia | 'sof':1
(1 ligne)

vv=> select to_tsquery('finnish','sof:*');
 to_tsquery
------------
 'sof':*
(1 ligne)

vv=>
vv=> select 'found sof', name
vv-> from name_fulltext
vv-> where searchable_index_col@@to_tsquery('finnish','sof:*');
 ?column?  | name
-----------+-------
 found sof | Sofia
(1 ligne)

vv=>
vv=> select 'notfound sofi', name
vv-> from name_fulltext
vv-> where searchable_index_col@@to_tsquery('finnish','sofi:*');
 ?column? | name
----------+------
(0 ligne)

vv=>
vv=> select 'found sofia', name
vv-> from name_fulltext
vv-> where searchable_index_col@@to_tsquery('finnish','sofia:*');
  ?column?   | name
-------------+-------
 found sofia | Sofia
(1 ligne)




--
                    Regards, Vincent Veyron

https://libremen.com/
Legal case, contract and insurance claim management software