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
Returnstrue
if its two arguments have a degree of similarity greater than the current similarity threshold set bypg_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.
The post "How To Enable String Similarity Features in PostgreSQL" appeared first on Writech.
Top comments (0)