Installing and Using PostgreSQL Modules

In this article, we will learn how to install and use the PostgreSQL modules chkpass, fuzzystrmatch, isn and hstore. Modules add different capabilities to a database, like admin and monitoring tools, new data types, operators, functions and algorithms. Let’s look at modules that add new data types and algorithms, which will help us to push some of the application logic to the database.

PostgreSQL has been called the “most advanced open source database”. I have been using it for the last four years as an RDBMS for Foodlets.in, and as a spatial data store at CSTEP (Center for Study of Science, Technology and Policy). PostgreSQL is one piece of software that doesn’t fail to impress me every now and then.

Installing the modules

Note: I am running Ubuntu 10.04 and PostgreSQL 8.4.

Install the postgresql-contrib package and restart the database server, then check the contrib directory for the list of available modules:

Using chkpass

The chkpass module will introduce a new data type, “chkpass”, in the database. This type is used to store an encrypted field, e.g., a password. Let’s see how chkpass works for a user account table that we create and insert two rows into:

SELECT count(*) from accounts where username='user1' and password = 'pass1'

The = operator uses the eq(column_name, text) in the module to test for equality. chkpass uses the Unix crypt() function, and hence it is weak; only the first eight characters of the text are used in the algorithm. chkpass has limited practical use; the pgcrypto module is an effective alternative.

Using fuzzystrmatch

This module installs the soundx(), difference(), levenshtein() and metaphone() functions. soundx() and metaphone() are phonetic algorithms — they convert a text string to a code string based on its pronunciation. difference() and levenshtein() return a numeric value based on the similarity of the two input strings.

Let’s now look into the levenshtein() and metaphone() functions. The Levenshtein distance between two strings is the minimum number of insertions, deletions or substitutions required to convert one string to another.

SELECT levenshtein('foodlets', 'booklets');

This query returns 2, as is obvious.

The metaphone() function takes a text string and the maximum length of the output code as its two input parameters. These examples return FTLTS:

SELECT metaphone('foodlets', 6);
SELECT metaphone('fudlets', 6);

If we try to get the Levenshtein distance between the returned strings, this returns 0:

SELECT levenshtein('FTLTS','FTLTS');

This means that the two words sound similar.

fuzzystrmatch is very helpful in implementing the search feature for a website. Now the search can work with alternate spellings and misspelled keywords. Reminds you of the “Did you mean…” feature on Google Search, right?

Using isn

This module will introduce data types to store international standard numbers like International Standard Book Numbers (ISBN), International Standard Music Numbers (ISMN), International Standard Serial Numbers (ISSN), Universal Product Codes (UPC), etc. It will also add functions to validate data, type-cast numbers from older formats to the newer 13-digit formats, and vice-versa.

(Actually, the name of the book mentioned here, Rework by Jason Fried, happens to be my favourite book on product/project management! I have prescribed it to all my teammates.)

Using hstore

You must have heard enough about NoSQL and key-value databases. It’s not always NoSQL vs relational databases — with the hstore module, PostgreSQL allows you to store data in the form of key-value pairs, within a column of a table.

Imagine you are processing spreadsheets and you have no idea about the column headers and the data type of the data in the sheets. That’s when hstore comes to your rescue! Incidentally, hstore takes keys and values as text; the value can be NULL, but not the key.

Let’s create a table with a column of type hstore and insert some rows: