资讯详情

提高模糊查询

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

开启pg_trgm扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

是否开启扩展

postgres=# \dxList of installed extensionsName   | Version |   Schema   |                            Description
---------+---------+------------+-------------------------------------------------------------------pg_trgm | 1.6     | public     | text similarity measurement and index searching based on trigramsplpgsql | 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 homeTable "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;