How to Use pg_trgm Extension in PostgreSQL for Text Search

To use the pg_trgm extension in PostgreSQL, first enable it with CREATE EXTENSION pg_trgm;. Then you can use its functions like similarity() and operators like % to perform fast text similarity searches and indexing.

Syntax

The pg_trgm extension is enabled using the CREATE EXTENSION command. After enabling, you can use its functions and operators for text similarity.

• CREATE EXTENSION pg_trgm; - Enables the extension in your database.
• similarity(text, text) - Returns a similarity score between 0 and 1.
• word_similarity(text, text) - Similarity based on word matching.
• % operator - Checks if two strings are similar above a threshold.
• pg_trgm also supports GIN or GIST indexes for fast searches.

sql

CREATE EXTENSION pg_trgm;

-- Example usage of similarity function
SELECT similarity('postgresql', 'postgre');

-- Using similarity operator
SELECT 'postgresql' % 'postgre';

Example

This example shows how to enable pg_trgm, create a table, insert data, and query using similarity functions and operators.

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT
);

INSERT INTO products (name) VALUES
('PostgreSQL Guide'),
('Postgres Tutorial'),
('PostgreSQL Extensions'),
('MySQL Guide');

-- Find products similar to 'Postgres'
SELECT name, similarity(name, 'Postgres') AS sim_score
FROM products
WHERE name % 'Postgres'
ORDER BY sim_score DESC;

Output

       name        | sim_score 
-------------------+-----------
 Postgres Tutorial |  0.571429
 PostgreSQL Guide  |  0.533333
 PostgreSQL Extensions | 0.466667
(3 rows)

Common Pitfalls

Common mistakes when using pg_trgm include:

• Not enabling the extension before using its functions.
• Forgetting to create GIN or GIST indexes on text columns for performance.
• Using similarity operator % without understanding the default similarity threshold (0.3).
• Expecting exact matches; pg_trgm is for fuzzy matching.

Always check if the extension is installed and consider adjusting the similarity threshold with set_limit() if needed.

sql

/* Wrong: Using similarity operator without enabling extension */
-- SELECT 'postgresql' % 'postgre'; -- ERROR: operator does not exist

/* Right: Enable extension first */
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'postgresql' % 'postgre';

Quick Reference

Feature	Description	Example
Enable Extension	Activate pg_trgm in your database	CREATE EXTENSION pg_trgm;
Similarity Function	Get similarity score between two strings	SELECT similarity('cat', 'bat');
Similarity Operator	Check if strings are similar above threshold	SELECT 'cat' % 'bat';
Set Similarity Threshold	Adjust threshold for % operator	SELECT set_limit(0.4);
Index Support	Create GIN/GIST index for fast search	CREATE INDEX ON table USING gin(column gin_trgm_ops);

Key Takeaways

Enable the pg_trgm extension with CREATE EXTENSION before using its features.

Use similarity() and % operator for fuzzy text matching in queries.

Create GIN or GIST indexes with gin_trgm_ops for faster searches on large text data.

Adjust similarity threshold with set_limit() to control match sensitivity.

pg_trgm is designed for approximate matching, not exact string comparison.