PostgreSQL: Advanced PostgreSQL

Transactions:
begin work;
// do query
commit work;

begin work;
// do query
rollback work; // undo

begin work;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; // serialize, data doesn't change
// do query
commit work;

begin work;
// do query. End with FOR UPDATE; this serializes & locks this transaction until I update or transaction ends
// do update
commit work;

INDEX: Speeds up searches
create index [indexname] on [tbl] ( [col1], [col2] );
drop index [indexname];
create unique index [indexname] on [tbl] ( [col1], [col2] );

CLUSTER: Speeds up searches by clustering large search results

VACUUM: Speeds up searches by cleaning up expired rows
vacuum [tbl];
vacuum analyze [tbl];

EXPLAIN: Shows speed & statistic on queries
explain select * from [tbl];

LIMIT:
select * from [tbl] limit #;
select * from [tbl] limit # offset #;

CURSOR: must take place inside a transaction
begin work;
declare [cursor name] cursor for select [col] from [tbl];
fetch # from [cursor name];
move #from [cursor name];
close [cursor name];
commit work;

Temporary tables
select * into temporary [tbl] from [tbl2] where [condition];

Alter table content:
alter table [tbl] rename to [tbl'];
alter table [tbl] rename column [col] to [col'];
alter table [tbl] add column [col] [type];
alter table [tbl] alter column [col] [type];
alter table [tbl] alter column [col] set default [value];
alter table [tbl] alter column [col] drop default;

Grant permission:
grant select on [tbl] to [user];
grant all on [tbl] to public;

Inheritance:
create table [tbl] ( ... );
create table [tb2] ( ... ) inherits ( [tbl1] );
select * from [tbl1]*; // access shared columns in tbl1 & tbl2

Views: Views cannot be directly modified, just queried
create view [viewname] as [select ...];
drop view [viewname];

Rules:
create rule [rulename] as on select to [tbl/view] do instead nothing;
create rule [rulename] as on insert to [tbl/view] do instead [what to do];
create rule [rulename] as on update to [tbl/view] do instead [what to do];
create rule [rulename] as on deleete to [tbl/view] do instead [what to do];

Constraints: NOT NULL, UNIQUE, PRIMARY KEY
create table [tbl] ( col1 INTEGER NOT NULL DEFAULT [value] );
create table [tbl] ( col1 INTEGER UNIQUE );
create table [tbl] ( col1 INTEGER PRIMARY KEY ); // UNIQUE+NOT NULL

create table [tbl] (
col1 INTEGER,
col2 INTEGER,
UNIQUE(col1, col2) // must be on its own line
);

create table [tbl] (
col1 INTEGER,
col2 INTEGER,
PRIMARY KEY(col1, col2) // must be on its own line
);

Foreign Key:
create table [tbl1] ( col INTEGER PRIMARY KEY );
create table [tbl2] ( col INTEGER REFERENCES [tbl1] );

Foreign Key Options:
create table [tbl2] ( col INTEGER REFERENCES [tbl1] on update CASCADE on delete SET NULL );

where:
NO ACTION = UPDATEs and DELETEs to the primary key are prohibited if referenced by a foreign key row. (Default)
CASCADE = UPDATEs to the primary key update all foreign key columns that reference it. DELETEs on the primary key cause the deletion of all foreign key rows that reference it.
SET NULL = UPDATEs and DELETEs to the primary key row cause the foreign key to be set to NULL.

CHECK: validity checking during insertion
create table [tbl] ( age INTEGER CHECK (age > 18) );
create table [tbl] ( gender CHAR(1) CHECK (gender IN ('M', 'F') );
create table [tbl] ( age INTEGER, gender CHAR(1), CHECK ( age > 18 and gender in ('M', 'F') ) );