installation in Ubuntu
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
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
# 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".
select products.sku, products.title from products where to_tsvector(title) @@ to_tsquery('Mastering'); sku | title ------------+--------------------------------- aspnet4 | Mastering ASP.NET 4.0
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.
string || string concatenates