Mastering PostgreSQL 12 - Third Edition

Taking advantage of pg_trgm

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

test=# CREATE EXTENSION pg_trgm;  
CREATE EXTENSION 

The pg_trgm extension is pretty powerful, and to show you 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 available, and PostgreSQL allows you to load the data directly:

test=# COPY t_location FROM PROGRAM          'curl https://www.cybertec-postgresql.com/secret/orte.txt';  
COPY 2354

curl (a command-line tool for fetching ...