Full text indexing will make searching in large amounts of text quite quick. It isn't built into PostgreSQL, rather it's available through an external ('contrib') module called 'TSearch2'.

There was a 'tsearch' module so don't get them confused. TSearch was available before version 8.0 and will be deprecated in the future.

Installing tsearch2 is quite easy. Once you install the contrib package, find the tsearch2.sql file and import it:

psql -d databasename < /path/to/tsearch2.sql

for example:

$ psql -d mydatabase < /usr/share/postgresql/contrib/tsearch2.sql

It will run through a series of commands (CREATE FUNCTION and CREATE OPERATOR) and then finish installing.

If you log in to psql:

psql -d mydatabase

and run \d to print out a list of tables, you will see extra tables:

public | pg_ts_cfg | table | tomcat
public | pg_ts_cfgmap | table | tomcat
public | pg_ts_dict | table | tomcat
public | pg_ts_parser | table | tomcat

We don't need to worry about these tables or what they do at the moment.

Now that's installed, we need to add full text indexing to our table.