Partial word search in with tsvector type column

Question

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?

You could start with the manual, that shows you how to use full text search: postgresql.org/docs/current/textsearch.html and postgresql.org/docs/current/functions-textsearch.html — Frank Heikens
– Frank Heikens, Commented Aug 3, 2022 at 23:47

Erwin Brandstetter · Accepted Answer · 2022-08-04 00:44:24Z

1

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:

Get partial match from GIN indexed TSVECTOR column

edited Aug 4, 2022 at 0:44

answered Aug 4, 2022 at 0:21

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Mehul Over a year ago

Hey, @Erwin, Your help was most useful for me. I came across one issue where to_tsvector("POC1_Table"::text) does not work with NULL values. (ts_vector is not created is any NULL is there in that row). Then, I used document = to_tsvector(coalesce(CAST (COL1 as text),'') || ' ' || coalesce( COL2 ,'')). Here COL1 is integer so need coalesce CAST and where COL2 is text. This will work even any value is NULL. Do you know any better way to convert integer, bigint, binary values in ts_vector?

Erwin Brandstetter Over a year ago

@Mehul: Just concat_ws(' ', col1, col2). You don't even need an explicit cast for this as concat_ws() coerces automatically. See: stackoverflow.com/a/66556331/939860 and stackoverflow.com/a/12320369/939860

Collectives™ on Stack Overflow

Partial word search in with tsvector type column

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related