postgresql – 自动完成字段的类似UTF-8字符串

背景

用户可以输入名称,系统应与文本匹配,即使用户输入或数据库字段包含重音(UTF-8)字符也是如此.这是使用pg_trgm模块.

问题

代码类似于以下内容：

SELECT
    t.label
  FROM
    the_table t
  WHERE
    label % 'fil'
  ORDER BY
    similarity( t.label,'fil' ) DESC

当用户键入fil时,查询匹配filbert但不匹配filépowder. (因为重音字符？)

解决方案#1失败

我尝试实现unaccent 函数并将查询重写为：

SELECT
    t.label
  FROM
    the_table t
  WHERE
    unaccent( label ) % unaccent( 'fil' )
  ORDER BY
    similarity( unaccent( t.label ),unaccent( 'fil' ) ) DESC

这只返回榛子.

解决方案#2失败

建议：

CREATE EXTENSION pg_trgm;
CREATE EXTENSION unaccent;

CREATE OR REPLACE FUNCTION unaccent_text(text)
  RETURNS text AS
$BODY$
  SELECT unaccent($1); 
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 1;

表中的所有其他索引都已删除.然后：

CREATE INDEX label_unaccent_idx 
ON the_table( lower( unaccent_text( label ) ) );

这只返回一个结果：

SELECT
    t.label
  FROM
    the_table t
  WHERE
    label % 'fil'
  ORDER BY
    similarity( t.label,'fil' ) DESC

题

重写查询以确保返回两个结果的最佳方法是什么？

谢谢！

有关

http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Unaccent_filtering_dictionary

http://postgresql.1045698.n5.nabble.com/index-refuses-to-build-td5108810.html

您没有使用pg_trgm模块提供的运算符类.我会创建一个像这样的索引：

CREATE INDEX label_Lower_unaccent_trgm_idx
ON test_trgm USING gist (lower(unaccent_text(label)) gist_trgm_ops);

最初,我在这里有一个GIN索引,但后来我才知道GiST可能更适合这种查询,因为它可以返回按相似性排序的值.更多细节：

> Postgresql: Matching Patterns between Two Columns
> Finding similar strings with PostgreSQL quickly

您的查询必须与索引表达式匹配才能使用它.

SELECT label
FROM   the_table
WHERE  lower(unaccent_text(label)) % 'fil'
ORDER  BY similarity(label,'fil') DESC -- it's ok to use original string here

然而,根据％运算符,“filbert”和“filépowder”实际上并非与“fil”非常相似.我怀疑你真正想要的是这个：

SELECT label
FROM   the_table
WHERE  lower(unaccent_text(label)) ~~ '%fil%'
ORDER  BY similarity(label,'fil') DESC -- it's ok to use original string here

这将找到包含搜索字符串的所有字符串,并根据％运算符首先对最佳匹配进行排序.

多汁的部分：表达式可以使用自Postgresql 9.1以来的GIN或GiST索引！我引用manual on the pg_trgm moule：

Beginning in Postgresql 9.1,these index types also support index
searches for LIKE and ILIKE,for example

如果你真的打算使用％运算符：

您是否尝试使用set_limit()降低相似度运算符％的阈值：

SELECT set_limit(0.1);

甚至更低？默认值为0.3.只是看看它是否是过滤额外匹配的阈值.

postgresql – 自动完成字段的类似UTF-8字符串

猜你在找的Postgre SQL相关文章