PostgreSQL

installation in Ubuntu

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Users

The default user is postgres. The postgres Linux account, being associated with the Postgres administrative role, has access to some utilities to create users and databases.

# to auth as postgres user
sudo -i -u postgres
# create users
createuser --interactive

Basic Usage

# to open the postgres prompt
psql

Connect to db

psql \
   --host=<DB instance endpoint> \
   --port=<port> \
   --username <master user name> \
   --password <master user password> \
   --dbname=<database name>

Full text search

In text retrieval, full-text search refers to techniques for searching a single computer-stored document or a collection in a full text database. Full-text search is distinguished from searches based on metadata or on parts of the original texts represented in databases. - Wikipedia

A Document is the unit of searching in a full text search system; for example, a magazine article or email message. - Postgres docs

tsvector is a built-in data type specifically for Full Text indexing. Convert columns to tsvector using the to_tsvector() function. This tokenizes and prepares the string for searching. Tokenize means to split each word, phrase, or symbol in the blob into an indexable token.

Pass the tsvector into the to_tsquery() function using @@ which says "return true if the tsvector matches the tsquery".

eg.

select products.sku, products.title from products where to_tsvector(title) @@ to_tsquery('Mastering');

    sku     |              title
------------+---------------------------------
 aspnet4    | Mastering ASP.NET 4.0

Aggregate Expressions

An aggregate expression represents the application of an aggregate function across the rows selected by a query. An aggregate function reduces multiple inputs to a single output value, such as the sum or average of the inputs.

Operators

string || string concatenates

Reference

http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/#1