Postgres 中的相似函数与 pg_trgm
我正在尝试使用 Postgres 中的相似性函数来进行一些模糊文本匹配，但是每当我尝试使用它时，我都会收到错误：
function similarity(character varying, unknown) does not exist
如果我向文本添加显式转换，则会收到错误：
function similarity(text, text) does not exist
我的查询是：
SELECT (similarity("table"."field"::text, %s::text)) AS "similarity", "table".* FROM "table" WHERE similarity > .5 ORDER BY "similarity" DESC LIMIT 10
我需要吗做些什么来初始化 pg_trgm？
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:
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
使用 postgresql 9.1：
安装后（在 ubuntu 上）
sudo apt-get install postgresql-contrib正如 tomaszbak 的回答。你只需要执行sql命令：
With postgresql 9.1:
after installing (on ubuntu)
sudo apt-get install postgresql-contribas tomaszbak answered.you just have to execute the sql command:
你必须安装pg_trgm。在 debian 中，获取此 sql：
/usr/share/postgresql/8.4/contrib/pg_trgm.sql。从命令行：或在 psql shell 内：
脚本默认安装在公共模式中，如果您想将其安装在其他位置，请编辑顶部的搜索路径（这样只需删除模式即可完成卸载/升级） ）。
You have to install pg_trgm. In debian, source this sql:
/usr/share/postgresql/8.4/contrib/pg_trgm.sql. From the command line:Or inside a psql shell:
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).
在 ubuntu 上，您需要运行
以获取 /usr/share/postgresql/8.4/contrib/pg_trgm.sql
On ubuntu you need to run
to get /usr/share/postgresql/8.4/contrib/pg_trgm.sql
如果您在
public架构中安装了pg_trgm扩展，未，则在使用相似性时必须显式指定架构像这样的功能If you have the
pg_trgmextension installed not in thepublicschema you must explicitly specify the schema when using thesimilarityfunction like this对于 Postgres 8.4 执行以下操作：
以 sudo 用户身份运行：
sudo apt-get install postgresql-contrib-8.4切换到 postgres 用户：
sudo su - postgres>运行：
psql -U DB_USER -d DB_NAME -f /usr/share/postgresql/8.4/contrib/pg_trgm.sql重新启动 postgres
For Postgres 8.4 do following:
As sudo user run:
sudo apt-get install postgresql-contrib-8.4Switch to postgres user:
sudo su - postgresRun:
psql -U DB_USER -d DB_NAME -f /usr/share/postgresql/8.4/contrib/pg_trgm.sqlRestart postgres
我在针对 Postgres 9.4 上使用 Django 1.11 ORM 进行三元相似性的函数运行 Django Test Runner 时遇到了同样的问题。
我必须做一些事情才能让它工作：
1) OP 是正确的，这需要启用 pg_trgm 扩展。然而，在 postgres9.4 中，这是基于每个数据库启用的。由于 Django 在每次运行时都会删除并重新创建测试数据库，因此新的测试数据库没有安装扩展。为了解决这个问题，我在 postgres 中默认新建的数据库模板中初始化了 pg_trgm 扩展。执行此操作的命令是以
postgres用户身份运行psql -d template1 -c 'CREATE EXTENSION pg_trgm;'。2) Postgres 必须重新启动
3) Django 测试运行程序无法识别这一点，所以我必须从 Django 1.11.12 升级到 1.11.18 （大概这在较新版本的 Django 中也已修复）
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.
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 thepg_trgmextension within the default newly-created database template in postgres. The command to do this ispsql -d template1 -c 'CREATE EXTENSION pg_trgm;'run as thepostgresuser.2) Postgres had to be restarted
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)
以防其他人也陷入困境：假设数据库名为“ttrss”，我切换到 postgresql 的命令行：
然后在那里安装扩展：
in case others are struggling too: assuming that the db is called “ttrss” I switched to the command line of postgresql:
then install the extension there: