Re: Full text search prefix matching

On Tue, 16 Dec 2014 14:59:51 +0200
Heikki Rauhala <heikki(dot)rauhala(at)reaktor(dot)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