PostgreSQL

installation in Ubuntu

  1. sudo apt-get update
  2. 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.

  1. # to auth as postgres user
  2. sudo -i -u postgres
  3. # create users
  4. createuser --interactive

Basic Usage

  1. # to open the postgres prompt
  2. psql

Connect to db

  1. psql \
  2.    --host=<DB instance endpoint> \
  3.    --port=<port> \
  4.    --username <master user name> \
  5.    --password <master user password> \
  6.    --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.

  1. select products.sku, products.title from products where to_tsvector(title) @@ to_tsquery('Mastering');
  3.     sku     |              title
  4. ------------+---------------------------------
  5.  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