PostgreSQL 9.2 Volume 1

1. Getting Started

PostgreSQL is relational database management system (RDBMS).

Architecture

Use client / server architecture. Server handles multiple concurrent connections from clients. It starts new process called "forks" for each new connection.

2. The SQL Language

Create DB: $ createdb my_database           $ dropdb my_database

                  $ /usr/local/pgsql/bin/ createdb my_database

Access DB $ psql my_database

my_database => SELECT version();

Internal commands

my_database --> \h    - help

my_database --> \q    - quit from plsql to shell

my_database --> \i    - read command from specific file

my_database --> \b    - backspace

my_database --> \f    - form feed

my_database --> \n    - newline

my_database --> \r    - carriage return

my_database --> \t    - tab

my_database --> \o, \oo, \ooo (o = 0 - 7) octal byte value

my_database --> \xh, \xhh (h = 0 - 9, A - F)   hexadecimal byte value

my_database --> \uxxxx, \Uxxxxxxxx (x = 0 - 9, A - F)  16 or 32-bit hexadecimal Unicode character value

Create table

CREATE TABLE films (
    code        char(5) PRIMARY KEY DEFAULT nextval('school_id_seq')
    vector  int[][], -- 2 dimension array,
    name   varchar(40) NOT NULL CHECK (name <> '')
    ddid     integer CHECK (did > 100),
    price numeric DEFAULT 9.99,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    modtime   timestamp DEFAULT current_timestamp, -- default value is current time
    CONSTRAINT production UNIQUE(date_prod),
    --CONSTRAINT code_title PRIMARY KEY(code,title)
);

Joining tables 
SELECT * FROM table1 a INNER JOIN table2 b ON (a.name = b.name); 
SELECT * FROM table1 a LEFT OUTER JOIN table2 b ON (a.name = b.name); 
SELECT * FROM table1 a RIGHT OUTER JOIN table2 b ON (a.name = b.name);

3. Advanced Features
Transaction concept (all-or-nothing)

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.

when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others. To facilitate these in PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands

If, partway through the transaction, we decide we do not want to commit (perhaps bank balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be cancelled. if you are sure you won't need to roll back to a particular savepoint again, it can be released, so the system can free some resources. Keep in mind that either releasing or rolling back to a savepoint will automatically release all save-points that were defined after it.

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00 
    WHERE name = 'Wally';
COMMIT;
END;

Window function

A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row. Behind the scenes, the window function is able to access more than just the current row of the query result. Not allowed within where clause. 

This shows how to compare each employee's salary with the average salary in his or her department.

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2

OVER clause directly following the window function's name and argument(s). The OVERclause determines exactly how the rows of the query are split up for processing by the window function. The PARTITION BY list within OVERspecifies dividing the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s).

When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for several functions. Instead, each windowing behavior can be named in a WINDOW clause and then referenced in OVER. For example:

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Inheritance

Inheritance is a concept from object-oriented databases. 

Ref: http://developer.postgresql.org/pgdocs/postgres/tutorial-inheritance.html

CREATE TABLE cities ( name text, population real, altitude int );
CREATE TABLE capitals ( state char(2))  INHERITS (cities);

On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 feet or higher:
SELECT name, altitude FROM ONLY cities WHERE altitude > 500;