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

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:

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related