For some of these examples, we will use the contrived table.
Demonstrates new ability to define primary/unique key deferrable
CREATE TABLE passengers(
    passenger_id serial PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, 
	passenger_name varchar(100), 
	weight integer, aisle varchar(10));
INSERT INTO passengers(passenger_name, weight, aisle)
	VALUES ('Jack', 200, '18'), ('Jill', 150, '20'),
		('Cathy', 150, '20') , ('Simon',1000, '18');
SELECT INTO and CREATE TABLE AS 
now return row counts to the client in their command tags
SELECT aisle, COUNT(*) as tally INTO passengers_tally
FROM passengers GROUP BY aisle;
-- result
SELECT 2

Allow IF EXISTS drop on columns and constraints
ALTER TABLE passengers_tally DROP COLUMN 
	IF EXISTS age ;
ALTER TABLE passengers_tally DROP CONSTRAINT 
	IF EXISTS pk_passengers_tally; 
	
Using exclusion constraints - PostGIS example 
- no overlapping point bounding box
CREATE TABLE poi(pt_id serial PRIMARY KEY, 
	pt geography(Point,4326));
ALTER TABLE poi ADD CONSTRAINT uidxb_poi 
	EXCLUDE USING gist (pt WITH &&);

-- swapping keys -- Note I can get away with this 
even though the update will before completion cause a key violation because passenger_id is marked as DEFERRED
UPDATE passengers 
    SET passenger_id = 
        CASE WHEN passenger_id = 4 THEN 1 ELSE passenger_id + 1 END;

--Using ORDERED aggregates and the new string_agg aggregate function
SELECT aisle, string_agg(passenger_name, '|' ORDER BY weight) As pass_list_by_wgt, 
	string_agg(passenger_name, '|' ORDER BY passenger_name) As pass_list_name,
	array_agg(DISTINCT weight ORDER BY weight)   As arr_weight
FROM passengers
GROUP BY aisle ORDER BY aisle;

-- results
 aisle | pass_list_by_wgt | pass_list_name | arr_weight
-------+------------------+----------------+------------
 18    | Jack|Simon       | Jack|Simon     | {200,1000}
 20    | Jill|Cathy       | Cathy|Jill     | {150}

--Using WINDOW FUNCTIONS ROWS
SELECT aisle, passenger_name, weight,
 SUM(weight) OVER (ORDER BY weight, aisle, passenger_name 
    ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING) As weight_look_2_ahead
FROM passengers
ORDER BY weight,aisle,passenger_name;
-- results --
 aisle | passenger_name | weight | weight_look_2_ahead
-------+----------------+--------+---------------------
 20    | Cathy          |    150 |                 500
 20    | Jill           |    150 |                1350
 18    | Jack           |    200 |                1200
 18    | Simon          |   1000 |                1000
WINDOW FUNCTION RANGE - tally weight of all people of equal or lower weight in same aisle
SELECT aisle, passenger_name, weight,
 SUM(weight) OVER (PARTITION BY aisle ORDER BY weight
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As weight_aisle_lower
FROM passengers
ORDER BY aisle,weight,passenger_name;
-- result --
 aisle | passenger_name | weight | weight_aisle_lower
-------+----------------+--------+--------------------
 18    | Jack           |    200 |                200
 18    | Simon          |   1000 |               1200
 20    | Cathy          |    150 |                300
 20    | Jill           |    150 |                300  

Anonymous function using new DO command 
- adds a date_add field to all tables in public schema that don't already have one
DO $$
DECLARE var_searchsql text;
BEGIN
    var_searchsql :=  string_agg('ALTER TABLE ' 
    || t.table_schema || '.' 
    || t.table_name 
    	|| ' ADD COLUMN date_add timestamp DEFAULT(current_timestamp)', ';') 
    				FROM information_schema.tables t 
        LEFT JOIN information_schema.columns c 
        ON (t.table_name = c.table_name AND 
            t.table_schema = c.table_schema AND c.column_name = 'date_add') 
    WHERE t.table_type = 'BASE TABLE' AND t.table_schema = 'public' 
    AND c.table_name IS NULL;
    IF var_searchsql > '' THEN
        EXECUTE var_searchsql;
    END IF;
END$$ language plpgsql;
		

function named parameters
CREATE FUNCTION random_data(num_records integer, 
		multiplier float) 
    RETURNS SETOF float AS
$$
    SELECT random()*i*$2
        FROM generate_series(1,$1) As i;

$$ language 'sql';

-- calling the function with named parameters --
SELECT foo.i 
FROM random_data(multiplier:= 1000, 
    num_records:= 10) As foo(i);

These examples will use these roles,database, schema
CREATE ROLE jungle;
CREATE ROLE regina LOGIN CREATEDB PASSWORD 'queen^warrior';
GRANT jungle TO regina;
CREATE ROLE leo LOGIN PASSWORD 'lion@king.dom';
CREATE DATABASE kingdom OWNER regina;

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM jungle;

Granting/Revoking permissions on existing tables in schema public
GRANT ALL PRIVILEGES ON 
	ALL TABLES IN SCHEMA public TO jungle;
Granting all permissions on future tables in schema public to jungle.  
-- There are some nuances we won't get into such as permissions are only granted 
-- to objects that the grantor has permissions to GRANT
-- using the optional [FOR role] option allows 
-- you to designate a different grantor role other than current user 
-- as long as current user/has rights to promote to said role.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
	GRANT ALL PRIVILEGES ON TABLES TO jungle
	GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO jungle;

ALTER DEFAULT PRIVILEGES IN SCHEMA public	
	GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO jungle;

SELECT pg_size_pretty(pg_tablespace_size('pg_default')) as tssize, 
	pg_size_pretty(pg_database_size('somedb')) as dbsize, 
	pg_size_pretty(pg_relation_size('someschema.sometable')) as tblsize;
	
--Example importing data to table sometable
--from tab delimited where NULLs appear as NULL	
COPY sometable FROM '/path/to/textfile.txt' USING DELIMITERS '\t' WITH NULL As 'NULL';
--Example importing data to table sometable
--from csv delimited that includes field headers
COPY sometable FROM 'C:/somefile.csv' WITH CSV HEADER;

--Example exporting a query to a comma separated (CSV) called textfile.csv
--setting NULLS to text NULL
COPY (SELECT * FROM sometable WHERE somevalue LIKE '%') TO '/path/to/textfile.csv' 
 WITH NULL As 'NULL' CSV HEADER QUOTE AS '"';

Vacuuming
vacuum analyze verbose;
vacuum sometable;
vacuum full;
--Kills all active queries in selected db and list out process id 
--and usename of process and if kill successful
SELECT procpid, usename, pg_cancel_backend(procpid) 
FROM pg_stat_activity 
WHERE datname = 'somedb';

--introduced in 8.3 - terminates backedn
SELECT procpid, usename, pg_terminate_backend(procpid) 
FROM pg_stat_activity 
WHERE datname = 'somedb';

CREATE DATABASE somedb
  WITH OWNER = someuser
  WITH ENCODING='UTF8' TEMPLATE=template0 TABLESPACE = pg_default
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252';
       
CREATE TABLE orders(order_id serial NOT NULL,
  order_addeddt timestamp with time zone,
  order_rating rating,
  CONSTRAINT pk_orders_order_id PRIMARY KEY (order_id)
);

CREATE TYPE rating AS 
 ENUM('none', 'bronze', 'silver', 'gold', 'platinum');

CREATE OR REPLACE FUNCTION first_element_state(anyarray, anyelement)
  RETURNS anyarray AS
$$
    SELECT CASE WHEN array_upper($1,1) IS NULL 
    	THEN array_append($1,$2) ELSE $1 END;
$$ LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION first_element(anyarray)
  RETURNS anyelement AS
$$
    SELECT ($1)[1] ; $$ LANGUAGE 'sql' IMMUTABLE; 
    
CREATE AGGREGATE first(anyelement) ( SFUNC=first_element_state,
  STYPE=anyarray, FINALFUNC=first_element);

SELECT o.order_id, o.order_date, o.approved_date,
 COUNT(i.item_id) As nlineitems, 
 SUM(i.unit_price*i.num_units) As total
FROM orders o 
 INNER JOIN orderitems i ON o.order_id = i.order_id
GROUP BY o.order_id, o.order_date, o.approved_date
HAVING SUM(i.unit_price*i.num_units) > 200
ORDER BY o.approved_date NULLS FIRST;

CTE Example (8.4+)
WITH pt(x,y) AS (
   SELECT 100*random(), 200*random()
    FROM generate_series(1,10) As i
),
pt2(x,y) AS 
( SELECT generate_series(1,2) As x, generate_series(4,5) )
SELECT pt.x + pt2.y AS a, pt2.x*pt.y AS b
FROM pt CROSS JOIN pt2;

UPDATE sometable SET somevalue = 5
WHERE sometable.somename = 'stuff';

UPDATE sometable
 SET calccount = s.thecount
  FROM (SELECT COUNT(someothertable.someid) as thecount, 
    someothertable.someid 
    FROM someothertable 
     GROUP BY someothertable.someid) s
   WHERE sometable.someid = s.someid;
 
DELETE FROM sometable
 WHERE somevalue = 'something';

INSERT INTO orders(order_addeddt, order_rating)
 VALUES ('2007-10-01 20:40', 'gold'), 
 ('2007-09-01 11:00 AM', 'silver'), 
   ('2007-09-02 10:00 PM', 'none'), ('2007-10-10 PM', 'bronze');
   
--Pre 8.1+ only supports single values inserts
INSERT INTO orders(order_addeddt, order_rating)
 VALUES ('2007-10-01 20:40', 'gold');
 
--This is a fast delete that deletes everything in a table so be cautious.  
--Only works on tables not referenced in foreign key constraints
TRUNCATE TABLE sometable;