Postmodern Examples Table of Contents Arrays
There are a couple of different ways to index a posgresql database, You could do the following to create an index, but the whole thing must be reindexed or it goes out of date. (And reindexing takes a long time)
A different way of indexing is to add a ts_vector column to the table as discussed in http://linuxgazette.net/164/sephton.html. Then you search against the ts_vector column, not the index.
Using this method, the search query is simpler, using the tsv column.:
Or using the :raw keyword
Postgres provides 2 functions for matching against tsvectors: plainto_tsquery and to_tsquery. plainto_tsquery takes every word in the search string and assumes that they should all be connected by an "and" operator ('&').
to_tsquery() provides boolean abilities, but each word in the search string must be separated by a boolean operator. ('&' '|' or '!') (and, or, not). Unfortunately, it will throw up on text which doesn't have the separators (no phrases). It will not accept text which is not separated by '&', '|' or '!'. It does accept braces, which are used to indicate operator precidence, but two tokens which translate to different lexemes directly after one another will cause totsquery() to fail.
After indexing, this seems to work. The following postmodern query will return the obvious records quickly.
The string "fat & (rat | cat) & ! dog" will look for records that have the word "fat" and either the word "rat" or the word "cat" but cannot also have the word "dog".
The string "super:*" will look for records with a words that begins with "super".
More information at Datatype text search