Shaikh Sonny Aman’s Blog

previously www.mailtoaman.com

PostgreSQL Cheat Sheet

Posted on | August 20, 2007 |

CREATE DATABASE

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) ASINSERT 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 integerAS '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 TRANSACTIONUPDATE 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;

Comments

2 Responses to “PostgreSQL Cheat Sheet”

  1. VoIP Blog
    April 6th, 2008 @ 11:30 am

    It is one of things I can never understand … how people can think that way. It’s so illogical that it can only be based upon moronity.

  2. beastmoon
    April 9th, 2008 @ 2:32 am

    Great post, just like always. When I’m entering your blog I’m always sure I won’t regret it. Continue writing.

Leave a Reply