How To Enable String Similarity Features in PostgreSQL

Comparing strings at the database level is a nice feature to have, especially considering that it is much faster and more efficient than doing it at the application level.

To take advantage of the string similarity features in PostgreSQL, you first have to enable them. Let’s now learn how to do it.

Why the pg_trgm PostgreSQL Module Is Useful

PostgreSQL 9.1 introduced functions and operators to compare strings via the pg_trgm module. As stated in the official documentation, “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.”

String comparison functions offered by pg_trgm include:

• similarity(text, text) → real 
  Returns a number from 0 to 1 to indicate the degree of similarity between the two arguments.

Likewise, operators to compare strings include:

• text % text → boolean
Returns true if its two arguments have a degree of similarity greater than the current similarity threshold set by pg_trgm.similarity_threshold.

You can find a list of all string similarity functions and operators supported by PostgreSQL via pg_trgm here.

If the pg_trgm module is not installed, whenever you try to use similarity functions or operators in queries, you will get an error. For example, let’s consider the following query:

SELECT similarity("table"."field"::text, %s::text) AS "similarity", "User".* FROM "table" WHERE similarity > .5 ORDER BY "similarity" DESC LIMIT 10

Without the pg_trgm module installed correctly, PostgreSQL will raise the following error:”function similarity(character varying, unknown) does not exist”

Enabling the pg_trgm Module

Since pg_trgm is considered a “trusted” module, any user who has CREATE privilege can install it. To do so and enable string similarity features in PostgreSQL, launch the following SQL command:

CREATE EXTENSION pg_trgm;

You may get this error:

ERROR: could not open extension control file "…/extension/pg_trgm.control": No such file or directory

In this case, you need to install the pg_trgm module in your Ubuntu server with the command below:

sudo apt install postgresql-contrib

Re-run the aforementioned CREATE query, and it should now work.

Et voilà, you can now take advantage of the string similarity features offered by PostgreSQL and forget about the “function similarity(character varying, unknown) does not exist” error!

Conclusion

In this article, you saw what the pg_trgm is, why it is useful, and how to install it in PostgreSQL. The pg_trgm module enables you to use several string similarity functions and operators in PostgreSQL. This is particularly useful because comparing strings for similarity at the database level is much faster than at the application level.

Thanks for reading! I hope that you found this article helpful.