PostgreSQL Cheat Sheet (to be continued)

= DATABASE =

CREATE DATABASE

CREATE DATABASE dbName;

CREATE DATABASE (command line)

Createdb dbName

DROP DATABASE (command line)

Dropdb dbName;

Backup a database (command line)

pg_dump dbName > dbName.sql

Backup all databases (command line)

pg_dumpall > pgbackup.sql

Restore a database (command line)

psql -f dbName.sql dbName

Restore a database by a specific user (command line)

psql -U username -f dbName.sql dbName

Run a SQL script (command line)

psql -f script.sql databaseName

= TABLE =

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
);

DROP TABLE

DROP TABLE tableName;

Add a primary key

ALTER TABLE tableName ADD PRIMARY KEY (id);

Add a column

ALTER TABLE tableName ADD COLUMN columnName ;

Create an INDEX

CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Table Maintenance

VACUUM ANALYZE table;

Reindex a database, table or index

REINDEX DATABASE dbName;

= SELECT STATEMENTS =

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 =

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;

= FUNCTION =

Create a Function

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

= MISC =

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;

= SAMPLES=

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;