具有pg_trgm的Postgres中的相似度函数

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？

5 个解决方案

#1

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).

该脚本默认安装在公共模式中，如果要将其安装在其他位置，则编辑顶部的搜索路径（以便可以通过删除模式来完成卸载/升级）。

#2

With postgresql 9.1:

使用postgresql 9.1：

after installing (on ubuntu) sudo apt-get install postgresql-contrib as tomaszbak answered.

安装后（在ubuntu上）sudo apt-get install postgresql-contrib为tomaszbak回答。

you just have to execute the sql command:

你只需要执行sql命令：

CREATE EXTENSION pg_trgm;

#3

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

#4

If you have the pg_trgm extension installed not in the public schema you must explicitly specify the schema when using the similarity function like this

如果您没有在公共模式中安装pg_trgm扩展，那么在使用像这样的相似性函数时必须显式指定模式

select schema.similarity(foo,bar) from schema.baz

#5

For Postgres 8.4 do 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

#1