postgresql tsvector partial text match

Question

I'm trying to create a PostgreSQL query to find a partial text inside a tsvector column.

I have a tsvector value like this "'89' 'TT7' 'test123'" and I need to find any rows that contains "%es%".

How can I do that?

I tried

select * from use_docs_conteudo
WHERE textodados @@ to_tsquery('es')

score 3 · Accepted Answer · edited May 23 '17 at 10:29

3

It looks like you want to use fast ILIKE queries for wild match. pg_trgm will be the right tool to go with. You can use POSIX regex rules for defining your query.

WITH data(t) AS ( VALUES
  ('test123! TT7 89'::TEXT),
  ('test123, TT7 89'::TEXT),
  ('test@test123.domain TT7 89'::TEXT)
)
SELECT count(*) FROM data WHERE t ~* 'es' AND t ~* '\mtest123\M';

Result:

 count 
-------
     3
(1 row)

Links for existing answers:

edited May 23 '17 at 10:29

Community

1
1

answered Jul 07 '16 at 00:05

Dmitry S

4,990
2
24
32

1

I need to use tsvector to perform a search that contains the argument without a prefix matching. If I have "motorcycle" and I'm trying to search "torc", that would work fine. How could I do it with tsvector and tsquery? – André Luís Oliveira Sep 24 '21 at 18:54

postgresql tsvector partial text match

1 Answers1