Search code examples
postgresqlmatchpartialtsvector

postgresql tsvector partial text match

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

  • 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: