Pitted Knowledge of the Almighty Olive: PostgreSQL 9.1 and Ubuntu 12.04

This is just a quick guide on how to get started using PostgreSQL on Ubuntu.

Install the PostgreSQL server using apt:
sudo apt-get install postgresql
Install the contrib add-on package. This provides additional tools and features, such as improved logging and administration functions.
sudo apt-get install postgresql-contrib
Install the GUI admin interface pgadmin3:
sudo apt-get install pgadmin3
Edit the file /etc/postgresql/9.1/main/postgresql.conf to allow TCP/IP connections to the server. Just uncomment the following line in the file:
listen_addresses = 'localhost'
Start the database
sudo service postgresql start
To automatically start the server run the following command:
sudo update-rc.d postgresql defaults
Put a password on the default postgresql user by opening up a connection to the database:
sudo -u postgres psql template1
And then running the following SQL command:
ALTER USER postgres WITH PASSWORD 'password';
Set up the same password for the system postgres user:
# Delete the existing postgres user password
sudo passwd -d postgres

# Set the user password
sudo su postgres -c passwd

You can create a database directly from the command line (this assumes that your linux user has a corresponding account to use PostgreSQL):
createdb newdb

To create a new database with a user that has full rights on that database:
# Create the user
sudo -u postgres createuser -D -A -P newuser# Create the database
sudo -u postgres createdb -O newuser newdb
The corresponding command to delete a database is:
dropdb olddb
Accessing the database through the commandline:
psql newdb
#OR you could use
sudo -u postgres psql newdb#OR you could use
psql -h localhost -U postgres -W newdb

NOTE: If the command prompt shows "=#", then you are using an admin account which bypasses normal access controls. This is potentially dangerous. Your average account should be displaying "=>" as part of the prompt.
In-built psql commands are prefaced with a '\':
- \h displays the help for SQL commands
- \? displays the help for psql-specific commands
- \q exits the terminal interface for psql
Some useful in-built SQL statements:

SELECT version(); will display the current PostgreSQL version
SELECT current_date; will display the current date of the system
SELECT now(); will display the date and time
SELECT now()::date; will only display the date portion
SELECT now()::time; will only display the time portion
count(), sum(), avg(), max(), and min() are special aggregate functions you can perform on fields in your SQL statement.

Pitted Knowledge of the Almighty Olive