postgresql Postgres 中的相似函数与 pg_trgm
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2254999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Similarity function in Postgres with pg_trgm
提问by Alex Gaynor
I'm trying to use the similarity function in Postgres to do some fuzzy text matching, however whenever I try to use it I get the error:
我正在尝试使用 Postgres 中的相似性函数进行一些模糊文本匹配，但是每当我尝试使用它时，我都会收到错误消息：
function similarity(character varying, unknown) does not exist
If I add explicit casts to text I get the error:
如果我向文本添加显式强制转换，则会出现错误：
function similarity(text, text) does not exist
My query is:
我的查询是：
SELECT (similarity("table"."field"::text, %s::text)) AS "similarity", "table".* FROM "table" WHERE similarity > .5 ORDER BY "similarity" DESC LIMIT 10
Do I need to do something to initalize pg_trgm?
我需要做些什么来初始化 pg_trgm 吗？
采纳答案by Tobu
You have to install pg_trgm. In debian, source this sql: /usr/share/postgresql/8.4/contrib/pg_trgm.sql. From the command line:
你必须安装 pg_trgm。在 debian 中，源这个 sql: /usr/share/postgresql/8.4/contrib/pg_trgm.sql。从命令行：
psql -f /usr/share/postgresql/8.4/contrib/pg_trgm.sql
Or inside a psql shell:
或者在 psql shell 中：
\i /usr/share/postgresql/8.4/contrib/pg_trgm.sql
The script defaults to installing in the public schema, edit the search path at the top if you want to install it somewhere else (so that uninstalling/upgrading can be done simply by dropping the schema).
该脚本默认安装在公共架构中，如果您想将其安装在其他地方，请编辑顶部的搜索路径（以便卸载/升级只需删除架构即可完成）。
回答by morja
With postgresql 9.1:
使用 postgresql 9.1：
after installing (on ubuntu) sudo apt-get install postgresql-contribas tomaszbak answered.
在安装（在 ubuntu 上）后，sudo apt-get install postgresql-contrib正如 tomaszbak 所回答的那样。
you just have to execute the sql command:
你只需要执行sql命令：
CREATE EXTENSION pg_trgm;
回答by tomaszbak
On ubuntu you need to run
在 ubuntu 上你需要运行
sudo apt-get install postgresql-contrib
to get /usr/share/postgresql/8.4/contrib/pg_trgm.sql
获取/usr/share/postgresql/8.4/contrib/pg_trgm.sql
回答by Kolyunya
If you have the pg_trgmextension installed notin the publicschema you must explicitly specify the schema when using the similarityfunction like this
如果您没有在架构中pg_trgm安装扩展，则在使用这样的功能时必须明确指定架构publicsimilarity
select schema.similarity(foo,bar) from schema.baz
回答by Haris Krajina
For Postgres 8.4do following:
对于 Postgres 8.4，请执行以下操作：
As sudo user run:
作为 sudo 用户运行：
sudo apt-get install postgresql-contrib-8.4
sudo apt-get install postgresql-contrib-8.4
Switch to postgres user:
切换到 postgres 用户：
sudo su - postgres
sudo su - postgres
Run:
跑：
psql -U DB_USER -d DB_NAME -f /usr/share/postgresql/8.4/contrib/pg_trgm.sql
psql -U DB_USER -d DB_NAME -f /usr/share/postgresql/8.4/contrib/pg_trgm.sql
Restart postgres
重启 postgres
回答by Robert Townley
I was having this same issue in the context of running the Django Test Runner against a function that uses the Django 1.11 ORM for trigram similarity on Postgres 9.4.
在针对使用 Django 1.11 ORM 在 Postgres 9.4 上进行三元组相似性的函数运行 Django Test Runner 时，我遇到了同样的问题。
I had to do a few things to get it working:
我必须做一些事情才能让它工作：
1) OP is correct that this required enabling the pg_trgmextension. However, in postgres9.4 this is enabled on a per-database basis. Since Django deletes and recreates the test database with each run, the new test database didn't have the extension installed. To fix this, I initialized the pg_trgmextension within the default newly-created database template in postgres. The command to do this is psql -d template1 -c 'CREATE EXTENSION pg_trgm;'run as the postgresuser.
1) OP 是正确的，这需要启用pg_trgm扩展。但是，在 postgres9.4 中，这是基于每个数据库启用的。由于每次运行 Django 都会删除并重新创建测试数据库，因此新的测试数据库没有安装扩展。为了解决这个问题，我pg_trgm在 postgres 中默认新创建的数据库模板中初始化了扩展。执行此操作的命令以用户psql -d template1 -c 'CREATE EXTENSION pg_trgm;'身份运行postgres。
2) Postgres had to be restarted
2) 必须重新启动 Postgres
3) The Django test runner wasn't recognizing this, so I had to upgrade from Django 1.11.12 to 1.11.18 (presumably this is also fixed in newer versions of Django)
3) Django 测试运行器无法识别这一点，所以我不得不从 Django 1.11.12 升级到 1.11.18（大概这在较新版本的 Django 中也已修复）
