Finding good matches

The queries shown so far are not the only queries that can cause issues. Just imagine searching for a name. Maybe, you are not aware of how to spell it precisely. You issue a query and the result is just an empty list. Empty lists can be pretty frustrating for end users. Frustration has to be avoided at any cost, so a solution is needed.

The solution comes in the form of the so-called distance operator (<->). It works like this:

test=# SELECT 'PostgreSQL' <-> 'PostgresSQL';
 ?column? 
----------
 0.230769
(1 row)

The distance between PostgreSQL and PostgresSQL is 0.23, so it is quite close. The distance can be between 0 (meaning identical) and 1 (totally different).

However, the question is not so much about the actual distance between two words but more about what is closest. Let's imagine we are looking for a village called Gramatneusiedl. Now an application designer cannot expect that somebody is able to spell this tricky name. So maybe, an end user might...