Database names get misspelled in real data, and "PostgreSQL" is no exception: users type Postgre, PostGreSQL, Postgres, or worse. PostgreSQL offers several independent mechanisms for finding rows anyway, each aimed at a different kind of mismatch.
The demonstrations below run against a small sample table of database names paired with a handful of variant spellings and typos.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE EXTENSION citext; CREATE TABLE t_database ( real_name citext ); INSERT INTO t_database VALUES ('PostgreSQL'), ('Postgres'), ('PostGreSQL'), ('postgres'), ('DB2'), ('DB/2 LUW'), ('DB/2'), ('IBM DB2'), ('Oracle'), ('MS SQL Server'), ('Microsoft SQL Server'); |
Case-insensitive comparison with citext
Wrapping columns in upper or lower works for case-insensitive matching, but it is clumsy and easy to forget, which invites bugs. The citext data type, supplied by the extension of the same name, moves the relaxed comparison to the type level: values keep their original casing, while comparisons ignore case.
|
1 2 3 4 5 6 |
test=# SELECT * FROM t_database WHERE real_name = 'postgresql'; real_name ------------ PostgreSQL PostGreSQL (2 rows) |
Because the behavior lives on the data type, every application querying the column inherits it. The citext extension is available on all public clouds, including Amazon AWS and Microsoft Azure.
Pattern matching and its limits
LIKE and ILIKE are the classic tools for similarity search. A pattern such as one containing "postgr" will return every listed variant of the name.
|
1 2 3 4 5 6 7 8 |
test=# SELECT * FROM t_database WHERE real_name ILIKE 'postgr%'; real_name ------------ PostgreSQL Postgres PostGreSQL postgres (4 rows) |
The catch is that pattern matching requires prior knowledge of the spelling. If the stored value were "BostgreSQL", a pattern anchored on "postgr" would return nothing. LIKE and ILIKE are useful, but frequently insufficient.
Trigrams for typos
The pg_trgm extension handles misspellings that pattern matching cannot. After installing it, the distance operator can be used to rank strings by similarity — for example, when searching for "db3" because the actual IBM product name "DB2" was misheard.
|
1 2 |
test=# CREATE EXTENSION pg_trgm; CREATE EXTENSION |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# SELECT real_name <-> 'db3', * FROM t_database ORDER BY real_name <-> 'db3'; ?column? | real_name ------------+---------------------- 0.6666666 | DB2 0.71428573 | DB/2 0.8 | IBM DB2 0.8181818 | DB/2 LUW 1 | Oracle 1 | MS SQL Server 1 | PostgreSQL 1 | Microsoft SQL Server 1 | Postgres 1 | PostGreSQL 1 | postgres (11 rows) |
Ordering by distance puts "DB2" and "DB/2" at the top, which is the desired outcome. pg_trgm is not a cure-all and should be applied with care, but it addresses one class of problems caused by bad data. It suits names and addresses; full text search targets a different need.
Full text search and word order
Full text search locates words inside text. A query for "server" and "microsoft" returns the matching row, and word order is irrelevant — what matters is only that all terms appear.
|
1 2 3 4 5 6 7 |
test=# SELECT real_name, to_tsvector(real_name) FROM t_database WHERE to_tsvector(real_name) @@ to_tsquery('server & microsoft'); real_name | to_tsvector ----------------------+---------------------------------- Microsoft SQL Server | 'microsoft':1 'server':3 'sql':2 (1 row) |
Phrase search applies when sequence matters: the query can require that a word directly follow another.
|
1 2 3 4 5 6 7 |
test=# SELECT real_name, to_tsvector(real_name) FROM t_database WHERE to_tsvector(real_name) @@ to_tsquery('microsoft <-> sql'); real_name | to_tsvector ----------------------+---------------------------------- Microsoft SQL Server | 'microsoft':1 'server':3 'sql':2 (1 row) |
Here the search requires "microsoft" followed by "sql". Requiring "microsoft" followed by "server" instead fails, because an intervening word is not permitted by a strict phrase.
|
1 2 3 4 5 6 |
test=# SELECT real_name, to_tsvector(real_name) FROM t_database WHERE to_tsvector(real_name) @@ to_tsquery('microsoft <-> server'); real_name | to_tsvector -----------+------------- (0 rows) |
PostgreSQL also lets a query specify how far apart the terms may be.
|
1 2 3 4 5 6 7 |
test=# SELECT real_name, to_tsvector(real_name) FROM t_database WHERE to_tsvector(real_name) @@ to_tsquery('microsoft <2> server'); real_name | to_tsvector ----------------------+---------------------------------- Microsoft SQL Server | 'microsoft':1 'server':3 'sql':2 (1 row) |
Beyond these mechanisms there are additional options for fuzzy search, such as pgsimilarity. One related consideration that is easy to overlook: full text search and its indexing interact with the optimal VACUUM policy, through effects such as the GIN pending list.
