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.
postgresql-contrib package and restart the database server, then check the
contrib directory for the list of available modules:
sudo apt-get install postgresql-contrib sudo /etc/init.d/postgresql-8.4 restart cd /usr/share/postgresql/8.4/contrib/ ls
Create a test database called
su postgres createdb module_test
hstore modules to the
module_test database by running the following commands:
psql -d module_test -f chkpass.sql psql -d module_test -f fuzzystrmatch.sql psql -d module_test -f isn.sql psql -d module_test -f hstore.sql
Let us now look at an example of how each of the modules is used.
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:
CREATE TABLE accounts (username varchar(100), password chkpass); INSERT INTO accounts(username, "password") VALUES ('user1', 'pass1'); INSERT INTO accounts(username, "password") VALUES ('user2', 'pass2');
We can authenticate users with a query like the one that follows:
SELECT count(*) from accounts where username='user1' and password = 'pass1'
= 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.
This module installs the
metaphone() are phonetic algorithms — they convert a text string to a code string based on its pronunciation.
levenshtein() return a numeric value based on the similarity of the two input strings.
Let’s now look into the
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.
metaphone() function takes a text string and the maximum length of the output code as its two input parameters. These examples return
SELECT metaphone('foodlets', 6); SELECT metaphone('fudlets', 6);
If we try to get the Levenshtein distance between the returned strings, this returns 0:
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?
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.
Let’s test this module for storing book information:
CREATE TABLE books(number isbn13, title varchar(100)) INSERT INTO books("number", title) VALUES ('978-03', 'Rework');
INSERT statement throws an error:
Invalid input syntax for ISBN number: "978-03". However, this works just fine:
INSERT INTO books("number", title) VALUES ('978-0307463746', 'Rework')
To convert a 10-digit ISBN to 13 digits, use the
INSERT INTO books("number", title) VALUES (isbn13('0307463745'), 'Rework')
(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.)
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:
CREATE TABLE kv_data( id integer, data hstore) INSERT into kv_data values (1, hstore('name', 'amit') || hstore('city', 'bangalore')), (2, hstore('name', 'raghu') || hstore('age', '26')), (3, hstore('name', 'ram') || hstore('age', '28'));
You can create your own keys like “height”, “favourite_book”, etc. The
|| operator is used for concatenation.
Now that we have a table and a few rows of data, let’s look at some
DELETE queries. To select rows with the value for “city” as “bangalore”, use the following query:
SELECT * from kv_data where data->'city' = 'bangalore'
To get the average age across the table (returns 27.0), use the query given below:
SELECT avg((data->'age')::integer) age from kv_data;
::integer is used to type-cast the text value to an integer, so that math operations can be performed on it.
To select and sort rows by “name” values, use:
SELECT * from kv_data order by data->'name' desc
Update the “city” value to “delhi” for all rows, as follows:
UPDATE kv_data SET data = data || ('city' => 'delhi');
Then, delete the “age” key (and values) from all rows, as shown below:
UPDATE kv_data set data = delete(data, 'age')
Next, delete rows with the “name” as “amit”:
DELETE from kv_data where data->'name' = 'amit'
Although not a full-fledged key-value storage,
hstore does provide us with the flexibility of a key-value database and the power of SQL queries.
Other useful modules
Here are some other modules you may find useful:
- Pgcrypto provides functions for hashing and encryption. It supports SHA, MD5, Blowfish, AES and other algorithms.
- Citext adds a case-insensitive text data type, which stores text in lower-case form.
- Uuid-ossp provides functions to generate universally unique identifiers.
- Pg_trgm adds functions to find text similarity based on trigram matching.