postgres 模糊匹配

1：前缀模糊 

　　create index index_table_field on table (left(address,3) varchar_pattern_ops);

　　select * from table where address  ~ '^abc';

`　　附链接：https://www.coder.work/article/2609033

2：后缀索引

　　craete index index_table_field on table using btree (reverse(address)));

　　select * from table where address ~ 'abc$';

3：中缀索引

　　1°：声明pg_trgm  create extension pg_trgm;


　　2°：create index index-table_field on table using gin(address gin_trgm_ops);

　　3°：select * from table where address ~ '?=abc';

　　4°：中文模糊查询需要转码：

##建立转换函数并声明为immutable类型
create or replace function textsend_i (text) returns bytea as
$$
 
  select textsend($1);
 
$$
language sql
strict immutable;

##创建索引
create index idx_name_1 on jd_daojia_product_1225 using gin(text(textsend_i(address)) gin_trgm_ops);

参考博文：

https://www.cnblogs.com/guoxueyuan/p/8625458.html

https://blog.csdn.net/u014539401/article/details/72794503

 

说明：后续会对pg_trgm进行详解