Achieving better answers with fuzzy searching

Performing precise searching is not the only thing expected by users these days. Modern websites have educated users in a way that they always expect a result, regardless of the user 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.

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 instruction:

test=# CREATE EXTENSION pg_trgm;  
CREATE EXTENSION

The pg_trgm extension is pretty powerful, and to show what it is capable of, I have compiled some sample data consisting of 2,354 names of villages and cities here in Austria, Europe.

Our sample data can be stored in a simple table:

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

My company website has all the data and PostgreSQL enables you to load the data directly...