开启pg_trgm扩展

提高模糊查询

提高前后模糊，SELECT * FROM population WHERE name LIKE '%三%';

开启pg_trgm扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

是否开启扩展

postgres=# \dx
                                    List of installed extensions
  Name   | Version |   Schema   |                            Description
---------+---------+------------+-------------------------------------------------------------------
 pg_trgm | 1.6     | public     | text similarity measurement and index searching based on trigrams
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

创建gin索引

CREATE INDEX 索引名 ON 表名 USING GIN (字段名 gin_trgm_ops);

create index idx_name_gin on home using GIN (p_name gin_trgm_ops);
CREATE INDEX

列出索引

postgres=# \d home
                           Table "public.home"
  Column   |            Type             | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+---------
 id        | text                        |           |          |
 hname     | text                        |           |          |
 adress    | text                        |           |          |
 create_ts | timestamp without time zone |           |          | now()
 update_ts | timestamp without time zone |           |          |
 p_name    | text                        |           |          |
 p_id      | text                        |           |          |
Indexes:
    "idx_name_gin" gin (p_name gin_trgm_ops)

删除索引

DROP INDEX IF EXISTS 索引名;

DROP INDEX IF EXISTS idx_name_gin;