Chapter 14 of Practical SQL covers PostgreSQL's excellent full-text search features.
To show these in action, I built a simple Django app that uses the PostgreSQL
websearch_to_tsquery() function to convert search terms to a
tsquery value, allowing for additional user controls. For example, placing words in quotes allows for adjacency queries. A minus sign allows for exclusion of a word.
The database contains nearly 90 speeches made by U.S. presidents to Congress from 1945 onward. Most are "State of the Union" speeches typically delivered at the beginning of the year along with other notable addresses, such as George W. Bush's address to Congress following the Sept. 11 attacks.
Because PostgreSQL full-text search converts text to lexemes—linguistic units in a given language, or word roots without the variations created by suffixes—searching for "education" will return results that also include "educate" and "educator."
Text for speeches from:
Some speeches may reflect remarks as prepared for delivery, not as actually delivered. Occasional light editing applied to address typos or formatting.