Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Implementing Full Text Indexing with PostgreSQL : Page 2

Although PostgreSQL doesn't include full-text indexing and search capabilities by default, you can install and use the TSearch2 module that ships with PostgreSQL to index and search large amounts of text efficiently.


advertisement
Installing TSearch2
There are three ways to get TSearch2 implemented in your PostgreSQL installation.

  1. Download and compile the source
  2. Download and install the contrib rpm (if you are running Linux)
  3. Purchase a binary distribution
Compiling PostgreSQL is outside the scope of this article. However, I will cover compiling and installing the TSearch2 module itself.

The TSearch2 module is included with the PostgreSQL source and can be found within the root of the tree at contrib/tsearch2.

After you have configured and installed PostgreSQL itself, you need to perform two more simple steps to get Tsearch2 installed. From the root of the PostgreSQL source tree, enter the following two commands:

cd contrib/tsearch2 make; make install

That's it. The module is now installed. Of course you still have to configure PostgreSQL for its use. For the examples in this article, you'll use a development database called Fingerless (long story) to test TSearch2.

To configure the database to use TSearch2 you need to execute the tsearch2.sql file against the fingerless database. To do that, enter the following psql command:

psql fingerless < tsearch2.sql

Executing the script produces some noisy output as it creates sample tables and indexes for testing Tsearch2. Don't worry about all this output. When the script completes it will have added the following tables to your database (see Table 1):

Table 1. A list of tables added by executing the tsearch2.sql file.
SchemaNameTypeOwner
publicpg_ts_cfgtablepostgres
publicpg_ts_cfgmaptablepostgres
publicpg_ts_dicttablepostgres
publicpg_ts_parsertablepostgres

You will also have a detailed list of new functions and data types. For more information on what the script actually adds to your database please see the tsearch2.sql file or the Web site documentation.

The billing application keeps the main billing details in the table named public.cmd_contact_task_entries.

The table has the structure shown in Table 2:

Table 2: Structure of the public.cmd_contact_task_entries table.
ColumnTypeModifiers
entry_idintegernot null default nextval('"cmd_contact_task_e_entry_id_seq"'::text)
task_idinteger 
creator_employee_idinteger  
descriptiontext  
time_spentinterval  
created timestamp with time zone  
billed timestamp with time zone  
hourly_rate numeric(6,2)  
internal_notes text default ''::text
contact_id integer  
note_id integer default 0

The table also has this index:



"cmd_contact_task_entries_pkey" primary key, btree (entry_id)



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date