We widely use PostgreSQL full-text search in our projects. It is fast, reliable, and doesn't add any additional technical complexity. But sometimes it may not work as you expect it too.
Usually we add a ts_vector
column right in our data tables. Let's see how it has it is done.
title | tsv |
---|---|
Handyman | ‘handyman’:1 |
Heating & Cooling | ‘cool’:2 ‘heat’:1 |
Painting | ‘paint’:1 |
Roofing | ‘roof’:1 |
Column tsv
is generated with to_tsvector
function from a title
column with a trigger function.
CREATE FUNCTION skills_tsv_update_trigger() RETURNS trigger AS $$
begin
new.tsv :=
to_tsvector('pg_catalog.english', COALESCE(new.title, ''))
return new;
end
$$ LANGUAGE plpgsql;
Then we can query this table using to_tsquery
fuction.
SELECT * FROM skills
WHERE
tsv @@ to_tsquery('pg_catalog.english', 'roof')
-- 1 record found
A record with Roofing
has been found, and this makes sense. But what should be found for handy
query? You can imagine that it will be a Handyman
skill and you are wrong. Before explaining why this happens, let's try another query.
SELECT * FROM skills
WHERE
tsv @@ to_tsquery('pg_catalog.english', 'roofing')
-- no records found
No records were found, even if we have a record with Roofing
title in our table.
The to_tsvector
function internally calls a parser which breaks the document text into tokens and assigns a type to each token. For each token, a list of dictionaries is consulted, where the list can vary depending on the token type. The first dictionary that recognizes the token emits one or more normalized lexemes to represent the token. For example, roofing
became roof
. Some words are recognized as stop words, like &
in our example. It is possible to have many different configurations in the same database, and predefined configurations are available for various languages. In our example we used the default configuration english for the English language.
to_tsquery
function parses user input and converts it to tsquery
data type. It uses the same dictionaries as to_tsvector
function. Let's try to understand what happens in two queries that were listed above.
The result of to_tsquery('pg_catalog.english', 'handy')
will be handi
because of pluralization rules for words ending on y
. The query listed below doesn't return any records because 'handyman':1
vector does not overlap with handi
query.
SELECT * FROM skills
WHERE
tsv @@ to_tsquery('pg_catalog.english', 'handy')
-- no records found
How we can cover this situation? As you remember you can change a configuration that is used to parse user input. Let's try a simple
configuration.
select to_tsquery('pg_catalog.simple', 'handy')
-- 'handy'
Looks like this is what we need, let's add this to our query.
SELECT * FROM skills
WHERE
tsv @@ to_tsquery('pg_catalog.english', 'handy')
OR
tsv @@ to_tsquery('pg_catalog.simple', 'handy')
-- 1 record found
A problem with Roofing
and roof
can be solved in the same way. Let's change the function that is used to populate tsv
column.
CREATE FUNCTION skills_tsv_update_trigger() RETURNS trigger AS $$
begin
new.tsv :=
to_tsvector('pg_catalog.english', COALESCE(new.title, '')) ||
to_tsvector('pg_catalog.simple', COALESCE(new.title, ''))
return new;
end
$$ LANGUAGE plpgsql;
title | tsv |
---|---|
Handyman | ‘handyman’:1,2 |
Heating & Cooling | ‘cool’:2 ‘cooling’:4 ‘heat’:1 ‘heating’:3 |
Painting | ‘paint’:1 ‘painting’:2 |
Roofing | ‘roof’:1 ‘roofing’:2 |
Now vector has more data and query below works as expected.
SELECT * FROM skills
WHERE
tsv @@ to_tsquery('pg_catalog.english', 'roofing')
-- 1 record found
Top comments (0)