F.43. pg_trgm_mchar
pg_trgm_mchar is a Postgres Pro extension that allows fast trigram-based search for similar mchar and mvarchar strings. The functionality provided by this extension overlaps substantially with the functionality of pg_trgm: it provides equivalent functions and operators adjusted for mchar and mvarchar types. However, pg_trgm_mchar supports only the GIN operator classes for searching these column types.
Note
The LIKE operator supports only (mchar, mvarchar) operand types. (mchar, mchar) operands are not supported.
F.43.1. Installation
pg_trgm_mchar is a built-in extension included into Postgres Pro Standard. It depends on both pg_trgm and mchar. You must install these modules before creating pg_trgm_mchar, or you can install all dependencies automatically with:
CREATE EXTENSION pg_trgm_mchar CASCADE;
Alternatively, if both required modules are already installed, you can enable pg_trgm_mchar using the following query:
CREATE EXTENSION pg_trgm_mchar;
Since mchar and mvarchar data types use the ICU library, it is also required for correct operation of the extension.
F.43.2. Example
Since pg_trgm_mchar operates almost identically to pg_trgm, for usage examples, refer to the pg_trgm documentation.
However, there is a difference in index creation processes. The pg_trgm_mchar extension uses the gin_trgm_ops_mvarchar/gin_trgm_ops_mchar operator family instead of gin_trgm_ops used by pg_trgm:
CREATE TABLE test_trgm(t mvarchar); CREATE INDEX trgm_idx ON test_trgm USING gin (t gin_trgm_ops_mvarchar); ... CREATE TABLE test_trgm_mchar(t mchar); CREATE INDEX trgm_mchar_idx ON test_trgm_mchar USING gin (t gin_trgm_ops_mchar);
In all other cases, these two modules behave identically.