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')

Answer 1

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),
  ('[email protected] TT7 89'::TEXT)
)
SELECT count(*) FROM data WHERE t ~* 'es' AND t ~* '\mtest123\M';

Result:

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

Links for existing answers:

• Postgresql full text search part of words
• PostgreSQL: Full Text Search - How to search partial words?