cancel
Showing results for 
Search instead for 
Did you mean: 

INCOMPLETE WORDS IN FULL TEXT SEARCH

SOLVED
Level 2 Adventurer

INCOMPLETE WORDS IN FULL TEXT SEARCH

Hi everyone,

 

Some years ago I did a similar question; but now I have a doubt when incomplete words, or words writed letters, for example.

 

The scenario is:

 

I have the next table, and data:

 

/* script for people table, with field tsvector and gin */

 

CREATE TABLE public.people (
id INTEGER,
name VARCHAR(30),
lastname VARCHAR(30),
complete TSVECTOR
)
WITH (oids = false);

 

CREATE INDEX idx_complete ON public.people
USING gin (complete);

 

/* data for people table */

INSERT INTO public.people ("id", "name", "lastname", "complete")
VALUES
(1, 'MICHAEL', 'BRYANT BRYANT', '''bryant'':2,3 ''michael'':1'),
(2, 'HENRY STEVEN', 'BUSH TIESSEN', '''bush'':3 ''henri'':1 ''steven'':2 ''tiessen'':4'),
(3, 'WILLINGTON STEVEN', 'STEPHENS FLINN', '''flinn'':4 ''stephen'':3 ''steven'':2 ''willington'':1'),
(4, 'BRET', 'MARTINEZ AROCH', '''aroch'':3 ''bret'':1 ''martinez'':2'),
(5, 'TERENCE BERT', 'CAVALIERE ENRON', '''bert'':2 ''cavalier'':3 ''terenc'':1');

 

I need retrieve the names and lastnames, according the tsvector field values. NOW I have the query:

 

SELECT * FROM people WHERE complete @@ to_tsquery ('english', ''' ' || 'STEVEN & FLINN' || ' ''' ) ORDER BY 1

 

And the result is right.  But, if I try with 

 

1-

SELECT * FROM people WHERE complete @@ to_tsquery ('english', ''' ' || 'STEVE & FLIN' || ' ''' ) ORDER BY 1

 

(without the last N in this name and lastname)

 

Or similar to:

 

2-

SELECT * FROM people WHERE complete @@ to_tsquery ('english', ''' ' || 'STAVEN & FLINN' || ' ''' ) ORDER BY 1

 

(STAVEN, not STEVEN)

 

I don't have results.  What can I do?

 

Thanks on advance, and thanks for your useful help.
Regards!

2 ACCEPTED SOLUTIONS

Accepted Solutions
EDB Team Member

Re: INCOMPLETE WORDS IN FULL TEXT SEARCH

Hi juancarlosun,

 

Hope you are doing good.

 

We have tried to reproduce the case at our end. We are using EPAS 9.6.

When I used the following query and got the below output.

edb=# SELECT * FROM people WHERE complete @@ to_tsquery ( 'STAVEN | FLINN' ) ORDER BY 1;

id | name | lastname | complete
----+-------------------+----------------+-------------------------------------------------
3 | WILLINGTON STEVEN | STEPHENS FLINN | 'flinn':4 'stephen':3 'steven':2 'willington':1
(1 row)


I have used | (or) operator here as ('STAVEN | FLINN'). So now its checking for any of the string.

Could you please try the above query and let us know if you are getting the same output.

Also could you please share the output of below command from your database.
edb=# SELECT VERSION();

 

Level 2 Adventurer

Re: INCOMPLETE WORDS IN FULL TEXT SEARCH

Hi SwagataBanik, Correct! It works for me! (version 9.4). Thanks for your time and knowledge! Regards.
2 REPLIES
EDB Team Member

Re: INCOMPLETE WORDS IN FULL TEXT SEARCH

Hi juancarlosun,

 

Hope you are doing good.

 

We have tried to reproduce the case at our end. We are using EPAS 9.6.

When I used the following query and got the below output.

edb=# SELECT * FROM people WHERE complete @@ to_tsquery ( 'STAVEN | FLINN' ) ORDER BY 1;

id | name | lastname | complete
----+-------------------+----------------+-------------------------------------------------
3 | WILLINGTON STEVEN | STEPHENS FLINN | 'flinn':4 'stephen':3 'steven':2 'willington':1
(1 row)


I have used | (or) operator here as ('STAVEN | FLINN'). So now its checking for any of the string.

Could you please try the above query and let us know if you are getting the same output.

Also could you please share the output of below command from your database.
edb=# SELECT VERSION();

 

Level 2 Adventurer

Re: INCOMPLETE WORDS IN FULL TEXT SEARCH

Hi SwagataBanik, Correct! It works for me! (version 9.4). Thanks for your time and knowledge! Regards.