F.53. pg_trgm_mchar — trigram-based search for similar mchar and mvarchar strings #

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.53.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.53.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.