The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings.

A trigram is a group of three consecutive characters taken from a string. We can measure the similarity of two strings by counting the number of trigrams they share. This simple idea turns out to be very effective for measuring the similarity of words in many natural languages.

The original module is located in GitHub. This module provides a new function and new operators which provide fuzzy searching for word in a text.

Note. Functions of this module and functions of pg_trgm module, which included in the PostgreSQL 9.6, are differ. Functions of this module have other names and the module does not provide GUC parameters.

This module available from GitHub under the same license as PostgreSQL and supports PostgreSQL 9.4+.

Before build and install pg_trgm you should ensure following:

• PostgreSQL version is 9.4 or higher.

Typical installation procedure may look like this:

$ git clone https://github.com/postgrespro/pg_trgm_pro
$ cd pg_trgm_pro
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck
$ psql DB -c "CREATE EXTENSION pg_trgm;"

The pg_trgm module provides the new functions.

The module provides new operators.

GiST and GIN indexes support the operator <%.

Let us assume we have an test_trgm table:

CREATE TABLE test_trgm (t text);

You can create GiST index:

CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

or GIN index:

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

Now you can use an index on the t column for substring similarity. For example:

SELECT t, substring_similarity('word', t) AS sml
  FROM test_trgm
  WHERE 'word' <% t
  ORDER BY sml DESC, t;

This will return all values in the text column that have a word which sufficiently similar to word, sorted from best match to worst. The index will be used to make this a fast operation even over very large data sets.

Oleg Bartunov [email protected]

Teodor Sigaev [email protected]