RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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.

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.

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.
entry_idintegernot null default nextval('"cmd_contact_task_e_entry_id_seq"'::text)
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:

   primary key, btree (entry_id)

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