Achieving better answers with fuzzy searching

Performing precise searching isn’t the only thing expected by users these days. Modern websites have taught users to always expect a result, regardless of their input. If you search on Google, there will always be an answer, even if the user input is wrong, full of typos, or simply pointless. People expect good results, regardless of the input data. This section will cover even more fuzzy string search techniques.

Taking advantage of pg_trgm

To do fuzzy searching with PostgreSQL, you can add the pg_trgm extension. To activate the extension, just run the following command:

test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

The pg_trgm extension is pretty powerful. To show you what it’s capable of, I’ve compiled some sample data consisting of 2,354 names of villages and cities here in Austria.

Our sample data can be stored in a simple table:

test=# CREATE TABLE t_location (name text);
CREATE TABLE

My...