Tutorial on installing and using the extension module on PostgreSQL, postgresql extension module

Tutorial on installing and using the extension module on PostgreSQL, postgresql extension module

Installation Module

Note: My runtime environments are Ubuntu 10.04 and PostgreSQL 8.4.

First, install the postgresql-contrib package and restart the database server. Then, check the contrib directory to see if there are some available modules:
 

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

Then we create a database named module_test:
 

su postgrescreatedb module_test

Then we apply chkpass, fuzzystrmatch, isn, and hstore modules to the module_test database and execute the following command:
 

psql -d module_test -f chkpass.sqlpsql -d module_test -f fuzzystrmatch.sqlpsql -d module_test -f isn.sqlpsql -d module_test -f hstore.sql

Next, let's take a look at how each module is used.
 
Use chkpass

The chkpass module introduces a new data type "chkpass" to store an encrypted field, such as a password. You can see from the SQL below that the string stored in the chkpass field is automatically encrypted:

 

CREATE TABLE accounts (username varchar (100), password chkpass);INSERT INTO accounts(username, "password" ) VALUES ( 'user1' , 'pass1' );INSERT INTO accounts(username, "password" ) VALUES ( 'user2' , 'pass2' );

Then we can perform identity authentication through the following SQL:
 

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

The = operator uses the eq (column_name, text) method. This method is provided by the chkpass module to test whether it is equal. Chkpass uses the Unix crypt () function, so the encryption effect is relatively weak. This function only encrypts the first eight digits of the string, as long as the first eight digits of the same string are considered to be equal. Therefore, it is not recommended to use the chkpass module in the actual production environment. We recommend that you use the pgcrypto module.
 
Use fuzzystrmatch

The functions provided by this module include soundx (), difference (), levenshtein (), and metaphone (). Soundx () and metaphone () are speech algorithms that convert text strings into Pronunciation-based code strings. Difference () and levenshtein () indicate the similarity between two input strings.

Let's first look at the levenshtein () and metaphone () functions:
 

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 obtain the Levenshtein distance between two strings, 0 is returned:
 

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

This means that the two strings are pronounced similarly.

Fuzzystrmatch is very useful in website search, and can be used for spelling check and keyword correction. It is equivalent to "Did you mean..." on Google ..."

Use isn

This module provides data types for storing International Standard values, such as International Standard Book Numbers (ISBN), International Standard Music Numbers (ISMN), and International Standard Serial Numbers (ISSN ), and so on. Verification functions and type conversion functions are also provided.

Let's test and store the book information:
 

CREATE TABLE books(number isbn13, title varchar (100))INSERT INTO books( "number" , title) VALUES ( '978-03' , 'Rework' );

The INSERT statement will be executed incorrectly because the input string is not a valid ISBN number, and the following statement can be correctly executed:
 

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

 
Use hstore

You must have heard a lot about NoSQL and key-value databases. Using the hstore module allows PostgreSQL to store key-value data.

Imagine that you are processing a table. You have no idea about the name and type of the table gree column header, and hstore can solve your problem. hstore uses both key and value as text storage, the value can be NULL, but the key is not allowed.

Create a table using the hstore type and insert some data to 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' , '26' )),(3, hstore( 'name' , 'ram' ) || hstore( 'age' , '28' ));

You can create your own keys, such as "height" and "favourite_book", and the | Operator is used for cascade and parallel operations.

Now we have a table and a small amount of test data. Next, let's take a look at how to query, modify, and delete the table. If we want to query the data whose city is bangalore, we can use the following SQL statement:
 

SELECT * from kv_data where data-> 'city' = 'bangalore'

To obtain the average age in a table, use the following statement:

 

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

Here: integer is used to convert text data into integers so that mathematical functions can be used.

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' );

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'

Although this is not a real key-value storage server, hstore provides sufficient flexibility as a NoSQL database.

Other useful modules

Here are a few modules you may use:

• Pgcrypto provides hash and encryption functions, and supports algorithms such as SHA, MD5, Blowfish, and AES.
• Citext adds the case-sensitive text data type and stores the data in lower case.
• Uuid-ossp provides a globally unique identifier Function
• Pg_trgm adds a string search function based on trigram matching.