CODEWITHSUNDEEP
sqlpostgresqlpattern-matchingfull-text-searchsql-like
Mehul
Mehul

Reputation: 19

Partial word search in with tsvector type column

I have added a table column document of tsvector type:

ALTER TABLE Schema.Table
ADD COLUMN document tsvector;

update Schema.Table
SET document = to_tsvector(Table::text);

LIKE does not work with tsvector type columns to search for a partial match. I would like to find 'Missing', 'Miss', 'Missplaced' etc. with a query like this (pseudo code, not working):

SELECT * FROM Schema.Table WHERE document ILIKE 'Miss%';

How can I search for a partial word in tsvector type column?

Upvotes: 0

Views: 810

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

Full text search allows prefix matching - with index support:

SELECT * FROM schema.table WHERE document @@ to_tsquery('simple', 'Miss:*');

Note :* appended to the (leading!) search word.
See:

Upvotes: 1

Related Questions