Postgres name search

Question

I have a tiny dataset (~1000 rows). Each row has a username, first name and last name. Can I do a fuzzy search on these three fields by using pg_trgm and concatenating the three fields together with two spaces between each? Alternatively, is there a better method to search through this set of users, using trigrams or any other method?

Clodoaldo Neto · Accepted Answer · 2017-04-13T18:46:09.127

2

select format('%s  %s  %s', username, first_name, last_name)
from t
order by greatest (
    similarity(_name, username),
    similarity(_name, first_name),
    similarity(_name, last_name)
) desc
limit 10

or

select s
from t, format('%s  %s  %s', username, first_name, last_name) s(s)
order by word_similarity(_name, s) desc
limit 10

edited Apr 13 '17 at 18:46

answered Apr 13 '17 at 18:34

Clodoaldo Neto

118,695
26
233
260

Why are there two spaces between `username` and `first_name`, but only one between `first_name` and `last_name`? – Shien Apr 13 '17 at 18:43
@Shien Just a typo – Clodoaldo Neto Apr 13 '17 at 18:46
More importantly, what happens when the user types in both the first name and username (separated by a space)? – Shien Apr 13 '17 at 19:07
how would you index this table? – jackbravo Sep 18 '20 at 04:54

Postgres name search

1 Answers1