cancel
Showing results for 
Search instead for 
Did you mean: 

PROBLEMS WITH FULL TEXT SEARCH

SOLVED
Highlighted
Level 2 Adventurer

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

 

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

 

And the result is right (the third record). 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 ACCEPTED SOLUTION

Accepted Solutions
Level 3 Adventurer

Re: PROBLEMS WITH FULL TEXT SEARCH

Hi,

 

I have tried the same sample data, and the o/p is coming for both queries in my local on PostgreSQL 9.4.4 version.

 

postgres=# select * from people ;

id |       name        |    lastname     |                    complete                     

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

  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

(5 rows)

 

postgres=# 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)

 

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

id |       name        |    lastname    |                    complete                     

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

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

(1 row)

 

5 REPLIES 5
EDB Team Member

Re: PROBLEMS WITH FULL TEXT SEARCH

Hi,

 

Thank you for raising this case.

 

Could you please let us know which exact version of Postgres you are using for this test case?

 

--Ankit

Level 2 Adventurer

Re: PROBLEMS WITH FULL TEXT SEARCH

Sure Ankit,

 

My PostgreSQL version is 9.4.

 

Thanks on advance!

Level 3 Adventurer

Re: PROBLEMS WITH FULL TEXT SEARCH

Hi,

 

I have tried the same sample data, and the o/p is coming for both queries in my local on PostgreSQL 9.4.4 version.

 

postgres=# select * from people ;

id |       name        |    lastname     |                    complete                     

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

  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

(5 rows)

 

postgres=# 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)

 

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

id |       name        |    lastname    |                    complete                     

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

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

(1 row)

 

Level 2 Adventurer

Re: PROBLEMS WITH FULL TEXT SEARCH

Ok @ameenabbas thanks for your time.  It is not my case; maybe by the version??  (me: 9.4, you: 9.4.4)

Regards!

Level 2 Adventurer

Re: PROBLEMS WITH FULL TEXT SEARCH

Sure Ankit, My PostgreSQL version is 9.4. Thanks on advance!