tutorial on installing and using extension modules on PostgreSQL _ database other

Installing modules

Note: My operating environment is Ubuntu 10.04 and PostgreSQL 8.4.

First install the Postgresql-contrib package and restart the database server, and then check the contrib directory to see if there are any available modules:

sudo apt-get install postgresql-contrib
sudo/etc/init.d/postgresql-8.4 restart
cd/usr/share/postgresql/8.4 /contrib/
ls

Then we create a database called Module_test:

Su postgres
createdb module_test

Then we apply the modules Chkpass, Fuzzystrmatch, isn and hstore to the Module_test database, and execute the following command:

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

Next, let's look at how each module is used.

using Chkpass

The Chkpass module introduces a new data type "Chkpass", which is used to store an encrypted field, such as a password. Using the method you can see from the following SQL, the string that is stored in the Chkpass field is automatically encrypted:

CREATE TABLE accounts (username varchar (MB), password Chkpass);
INSERT into accounts (username, "password") VALUES (' User1 ', ' Pass1 ');
INSERT into accounts (username, "password") VALUES (' User2 ', ' pass2 ');

We can then authenticate by using the following SQL:

SELECT Count (*) from accounts where Username= ' user1 ' and password = ' Pass1 '

Where the = operator uses the EQ (column_name, Text) method, which is provided by the Chkpass module to test for equality. Chkpass uses the Unix crypt () function, so the encryption effect is weak, and the function encrypts only the first 8 bits of the string, as long as the first 8-bit strings are considered equal. Therefore, it is not recommended to use the Chkpass module in the actual production environment, and the Pgcrypto module is recommended.

using Fuzzystrmatch

The functions provided by this module include: Soundx (), Difference (), Levenshtein (), and Metaphone (). Soundx () and Metaphone () are speech algorithms that convert text strings to code strings that are based on pronunciation. The difference () and Levenshtein () return a value representing the similarity of the two input strings.

Let's look at the Levenshtein () and Metaphone () functions First:

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

The query returns 2, obviously.

The Metaphone () function requires two parameters, one is a text string, and the other is the maximum length of the output code:

SELECT metaphone (' Foodlets ', 6);
SELECT metaphone (' Fudlets ', 6);

If you try to get a levenshtein distance of two strings, that will return 0:

SELECT Levenshtein (' ftlts ', ' ftlts ');

This means that the two strings are pronounced similarly.

Fuzzystrmatch is useful for implementing search functions on a Web site, and can be used to implement spell checking and error-key correction, which is equivalent to Google's "Did You Mean ..."

Using isn

This module provides data types for storing international standard values, such as International Standard book Numbers (ISBN), International Standard Music Numbers (ISMN), Internati Onal Standard serial Numbers (ISSN), Universal Product codes (UPC), and so on. It also provides validation functions, type conversion functions, and so on.

Let's test stored book information:

CREATE TABLE Books (number isbn13, title varchar)
inserts into the books ("number", title) VALUES (' 978-03 ', ' Rewor K ');

The INSERT statement will perform an error because the string you entered is not a valid ISBN number, and the following statement executes correctly:

INSERT into Books ("number", title) VALUES (' 978-0307463746 ', ' rework ')

If you want to convert a 10-bit ISBN to 13-bit, you can use the Isbn13 () function:

INSERT into Books ("number", title) VALUES (Isbn13 (' 0307463745 '), ' rework ')

using Hstore

You must have heard a lot about NoSQL and Key-value databases, and using the Hstore module allows PostgreSQL to have key-value storage capabilities.

Imagine you're working on a table where you don't have any idea about the name and type of the Table Force column header, and hstore can solve your problem, hstore the key and value as text, and the value can be NULL, but key is not allowed.

Let's create a table that uses the Hstore type and insert some data into the table:

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 ', ') '),
(3, Hstore (' name ', ' Ram ') || Hstore (' Age ', ' 28 '));

You can create your own key, such as "height", "Favourite_book", and so on, and | Operator is used for cascading parallel operations.

Now that we have a table and a small amount of test data, let's look at how to do the query, change, and delete operations, assuming that we want to query city for Bangalore data, you can use the following SQL statement:

SELECT * from Kv_data where data-> ' city ' = ' Bangalore '

To get the average age in the table, you can use the following statement:

SELECT avg ((data-> ' age '):: integer) age from Kv_data;

Here:: integer is used to turn text data into integers so that you can use mathematical functions.

To sort by name:

SELECT * from Kv_data ORDER by data-> ' name ' desc

Change all cities to Delhi:

UPDATE kv_data SET data = Data | | (' City ' => ' Delhi ');

And then delete the age key:

UPDATE kv_data Set data = delete (data, ' age ')

Or delete a record named Amit:

DELETE from Kv_data where data-> ' name ' = ' Amit '

While this is not a real key-value storage server, Hstore still offers enough flexibility to do the NoSQL database.

Other useful modules

Here are a few more modules you might use:

• Pgcrypto provides hash and cryptographic functions, and supports algorithms such as SHA, MD5, Blowfish, AES, etc.
• Citext adds case sensitive text data types, data is stored in lowercase
• UUID-OSSP a function that provides a global unique identifier
• PG_TRGM adds a string lookup function based on trigram matching