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.
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 is a concept from object-oriented databases.
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;
