to_tsquery query for phone numbers

user_292299 0
Viewed 463

We have a postgres database and a good amount of indexing using tsvector of all the text search attributes we might need for search, and the usage of ts_query is highly performant in our postgres database. But one possible search condition is phone numbers, where we have to support every possible format the user might search for.

Let's say my phone number on the tsvector is stored like "12985345885" and the user searches for 2985345885, how do I handle that in ts_query?

Basically:

select
('12985345885')::tsvector @@ ('12985345885:*')::tsquery

is true

and

select
('12985345885')::tsvector @@ ('2985345885:*')::tsquery

is false. It appears that postgres tsquery doesn't support a wildcard prefix?

full-text-search postgresql
1 Answers

Full-text search is not the right tool for that, because it is designed to search for words.

I would go for a substring search and a trigram index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ON mytab USING gin (phone gin_trgm_ops);

SELECT * FROM mytab WHERE phone LIKE '%2985345885%';
user_64643080
Related