Mastering PostgreSQL 9.6

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:

test=# COPY t_location FROM PROGRAM 'curl www.cybertec.at/secret/orte.txt';COPY 2354

Note that curl (a command-line tool to fetch data) has to be installed. ...