Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to install the expansion module in PostgreSQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Installation module

First install the postgresql-contrib package and restart the database server, then check the contrib directory to see if it contains 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 called module_test:

Su postgrescreatedb module_test

Then we apply the modules chkpass, fuzzystrmatch, isn and hstore to the module_test database by executing 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 look at how each module is used.

Use chkpass

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

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

Then we can authenticate through 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 crypt () function of Unix, so the encryption effect is weak, this function encrypts only the first eight bits of a string, as long as the first eight bits of the same string are considered equal. Therefore, it is not recommended to use the chkpass module in the actual production environment, but the pgcrypto module is recommended.

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 () return values that represent the similarity between the two input strings.

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

SELECT levenshtein ('foodlets',' booklets')

The query returns 2, which is obvious.

The metaphone () function takes two arguments, 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 the Levenshtein distance between two strings, it returns 0:

SELECT levenshtein ('FTLTS',' FTLTS')

This means that the two strings sound similar.

Fuzzystrmatch is very useful for websites to implement search functions, and can be used to implement spelling checking and error keyword correction, which 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), International Standard Serial Numbers (ISSN), Universal Product Codes (UPC), and so on. At the same time, it provides check function, type conversion function and so on.

Let's test the storage of book information:

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

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

INSERT INTO books ("number", title) VALUES ('978-0307463746', 'Rework')

To convert a 10-bit ISBN to a 13-bit one, 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 have the function of key-value storage.

Imagine you are dealing with a table, you have no idea about the name and type of the table force column header, and hstore can solve your problem. Hstore stores both key and value as text, and the value can be NULL, but key does not allow it.

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',' 26'), (3, hstore ('name',' ram') | | hstore ('age',' 28')

You can create your own key, such as "height", "favourite_book", etc., while the | | operator is used for cascading juxtaposition operations.

Now that we have a table and a small amount of test data, let's take a look at how to query, change, and delete. Suppose 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 get the average age in the table, you can use the following statement:

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

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

To sort by name, the method is:

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 key age:

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

Or delete the record named amit:

DELETE from kv_data where data- > 'name' =' amit'

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

The above is the editor for you to share how to install the expansion module in PostgreSQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.