Blog / 2010 / July / 16

Soundex function in postgres

July 16, 2010

The soundex function is not installed by default in the postgres database. To install on debian lenny:

$ apt-get install postgresql-contrib

This installs the script. You now need to run the fuzzystrmatch.sql script to install the function.

host:/home/drumcoder# su postgres
postgres@host:/home/drumcoder$ psql

Welcome to psql 8.3.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \i /usr/share/postgresql/8.3/contrib/fuzzystrmatch.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

postgres=# select soundex('hello world!');
 soundex 
---------
 H464
(1 row)

To get this to work from my specific database I had run the script again, after connecting to my database as the postgres user:

postgres=# \c my_database
my_database=# \i /usr/share/postgresql/8.3/contrib/fuzzystrmatch.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
my_database=#

Tags: soundex postgres