How To Enable String Similarity Features in PostgreSQL
Tags: data, database, postgres, programming
Published: 2024-01-19 21:15:18

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](https://www.postgresql.org/docs/14/pgtrgm.html), "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`](https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.42.7).

You can find a list of all string similarity functions and operators supported by PostgreSQL via `pg_trgm` [here](https://www.postgresql.org/docs/14/pgtrgm.html#id-1.11.7.42.6).

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:

```sql
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`](https://www.postgresql.org/docs/current/ddl-priv.html) [privilege](https://www.postgresql.org/docs/current/ddl-priv.html) can install it. To do so and enable string similarity features in PostgreSQL, launch the following SQL command:

```sql
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:

```shell
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](https://writech.run/blog/how-to-enable-string-similarity-features-in-postgresql-d40700c2d6bd/)" appeared first on [Writech](https://writech.run)._