Search code examples
sqlpostgresqlpattern-matchingfull-text-searchsql-like

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?

Solution

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