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

Community · Accepted Answer · 2017-05-23 10:29:12Z

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),
  ('[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:

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Jul 7, 2016 at 0:05

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

André Luís Oliveira Over a year ago

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?

Collectives™ on Stack Overflow

postgresql tsvector partial text match

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related