How Can I Perform Accent-Insensitive Queries in PostgreSQL?

Does PostgreSQL support accent-insensitive collation?

Microsoft SQL Server provides an "accent-insensitive" collation, allowing a query like SELECT * FROM users WHERE name LIKE 'João' to be executed to retrieve rows with the name "Joao".

Solution for PostgreSQL

PostgreSQL provides the unaccent module, which removes diacritics from strings. To use it, install the extension and use the unaccent() function:

SELECT * FROM users WHERE unaccent(name) = unaccent('João');

Index Optimization

To speed up queries, create an expression index:

CREATE INDEX users_unaccent_name_idx ON users(unaccent(name));

Remember to qualify functions and dictionaries in mode for safety reasons:

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.immutable_unaccent('public.unaccent', )  -- 限定函数和字典的模式
$func$;

CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));

Advanced Features

ligature

In PostgreSQL 9.6 and later, unaccent() correctly expands ligatures, converting "Œ" to "OE" and "ß" to "ss".

Pattern matching

Using unaccent in conjunction with the pg_trgm module allows you to perform arbitrary pattern matching using LIKE or ILIKE and create a triplet GIN or GIST expression index.

For simplicity, consider using triplet indexing for left-anchored patterns.

The above is the detailed content of How Can I Perform Accent-Insensitive Queries in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!