PostgreSQL: Basic PostgreSQL & SQL

After years of messing with MySQL, I am finally getting into PostgreSQL. The transition was not as easy as I had hoped.

To enter a database:
psql [dbname] -U [username]

Get list of SQL commands: \h
Get help: \?
To exit a database: \q
To clear the buffer: \r
To print the current query: \p
List all tables: \d
Expanded display (instead of columns): \x
List all functions: \df
List all functions named X: \df [function name]
Describe a function: \dd [function name]
Describe an operator: \do [operator ie. ! ^ * + -]

Commentting:
-- a single line comment
/* */ multi-line comment

Create new user:
create user [username] with password '[password]' [createdb/nocreatedb] in group [groupname] valid until 'Jan 1 2005';

Create database:
create database [dbname] with owner [1], [2] encoding = 'UTF8';

Common data types:
CHAR(length), VARCHAR(length)
INTEGER, FLOAT, NUMERIC(precision/decimal)
DATE, TIME, TIMESTAMP

Create table:
create table [tblname] (firstname CHAR(15), lastname CHAR(20), age INTEGER);
create table [tblname] (firstname CHAR(15) default 'no name');

Delete table:
drop table [tblname];

Describe specific table:
\d [tblname];

Insert table content:
insert into [tblname] values ('firstname', 'lastname', 20);

Delete table content:
delete from [tblname]; (deletes all)
delete from [tblname] where [condition];

Update table content:
update [tblname] set [colname]=[new value] where [condition];

Display table content:
select * from [tblname] where age<=20; select * from [tblname] where age>=18 and firstname='danny';

Display table content w/ specified column name:
select [colname] as [tmp-alt colname] from [tblname] order by [tmp-alt colname];
select 1+3 as total;

Display table content w/ specific ordering:
select * from [tblname] where [condition] order by [col1] DESC, [col2], [col3] DESC;

Conditional query:
select [colname] case when [condition w/ colname] then [conditional string value for yes] else [conditional string value for no] end as [casename] from [tblname];
select [colname] case when [condition 1 w/ colname] then [case string value 1 for yes] when [condition 2 w/ colname] then [case string value 2 for yes] else [string value for no] end as [casename] from [tblname];

Grabbing distinct column values from table:
select distinct [colname] from [tblname];
select distinct [col1], [col2] from [tblname];

Example Conditions:
age <> 99
age IS NULL
age != 22 and (firstname='jack' or firstname='bob')
age between 33 and 35

Like Conditions:
like 'D%' (begins with D)
like '%D%' (contains a D)
like '_D%' (has D in second position)
like 'D_nny' (Donny, Danny, Denny)
like 'D%e%' (begins with D contains e)
like 'D%e%f%' (begins with D contains e, then f)
not like 'D%' (begins with non-D)

Common vars:
select current_user;
select current_timestamp;