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-13 18:46:09Z

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, 2017 at 18:46

answered Apr 13, 2017 at 18:34

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

4 Comments

Shien Over a year ago

Why are there two spaces between username and first_name, but only one between first_name and last_name?

Clodoaldo Neto Over a year ago

@Shien Just a typo

Shien Over a year ago

More importantly, what happens when the user types in both the first name and username (separated by a space)?

jackbravo Over a year ago

how would you index this table?

Collectives™ on Stack Overflow

Postgres name search

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related