cancel
Showing results for 
Search instead for 
Did you mean: 

PROBLEMS WIT FULL TEXT SEARCH

Level 2 Adventurer

PROBLEMS WIT FULL TEXT SEARCH

Hi everyone,

 

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. Actually I have the query:

 

SELECT * FROM people WHERE complete @@ to_tsquery('WILLINGTON & FLINN');

 

And the result is right (the third ). BUT if I try with:

 

SELECT * FROM people WHERE complete @@ to_tsquery('STEVEN & FLINN');
/* the same record! */

 

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

 

Thanks on advance.
Regards

1 REPLY
Highlighted
EDB Team Member

Re: PROBLEMS WIT FULL TEXT SEARCH

Hi,

 

Your last two statement looks contradict. I see the below results for the below query :

 

edb=# SELECT * FROM people WHERE complete @@ to_tsquery('STEVEN & FLINN');

id |       name        |    lastname    |                    complete                     

----+-------------------+----------------+-------------------------------------------------

  3 | WILLINGTON STEVEN | STEPHENS FLINN | 'flinn':4 'stephen':3 'steven':2 'willington':1

(1 row)

 

Please confirm if you are able to see the same result here or you see no result for the above query. Please clarify. Also, on which database version are you running this?