PostgreSQL Cheat Sheet

I am sure I stole this from somewhere but I cannot find the source. Please take this with a degree of plagarism.

Start off:

CREATE DATABASE dbName;

CREATE TABLE (with auto numbering integer id)

CREATE TABLE tableName (
 id serial PRIMARY KEY,
 name varchar(50) UNIQUE NOT NULL,
 dateCreated timestamp DEFAULT current_timestamp
);

Add a primary key:

ALTER TABLE tableName ADD PRIMARY KEY (id);

Create an INDEX:

CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Backup a database (command line):

pg_dump dbName > dbName.sql

Backup all databases (command line):

pg_dumpall > pgbackup.sql

Run a SQL script (command line):

psql -f script.sql databaseName

Search using a regular expression:

SELECT column FROM table WHERE column ~ 'foo.*';

The first N records:

SELECT columns FROM table LIMIT 10;

Pagination:

SELECT cols FROM table LIMIT 10 OFFSET 30;

Prepared Statements:

PREPARE preparedInsert (int, varchar) AS
  INSERT INTO tableName (intColumn, charColumn) VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
DEALLOCATE preparedInsert;

Create a Function:

CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer
 AS 'SELECT date_part(''month'', $1)::integer;'
LANGUAGE 'sql';

Table Maintenance:

VACUUM ANALYZE table;

Reindex a database, table or index:

REINDEX DATABASE dbName;

Show query plan:

EXPLAIN SELECT * FROM table;

Import from a file:

COPY destTable FROM '/tmp/somefile';

Show all runtime parameters:

SHOW ALL;

Grant all permissions to a user:

GRANT ALL PRIVILEGES ON table TO username;

Perform a transaction:

BEGIN TRANSACTION
 UPDATE accounts SET balance += 50 WHERE id = 1;
COMMIT;

Basic SQL

Get all columns and rows from a table:

SELECT * FROM table;

Add a new row:

INSERT INTO table (column1,column2)
VALUES (1, 'one');

Update a row:

UPDATE table SET foo = 'bar' WHERE id = 1;

Delete a row

DELETE FROM table WHERE id = 1;