Turn: PostgreSQL Cheat Sheet

PostgreSQL Cheat sheetcreate DATABASE

DbName;

CREATE TABLE (with auto numbering integer ID)

TableName (ID serial PRIMARY KEY, name varchar () UNIQUE not NULL, dateCreated timestamp DEFAULT current_timestamp);

ADD a primary key

TableName ADD PRIMARY KEY (ID);

Create an INDEX

IndexName On TableName (ColumnNames);

Backup a Database (command line)

DbName dbName. sql

Backup all databases (command line)

Pg_dumpall > Pgbackup.sql

Run a SQL script (command line)

DatabaseName

Search using a regular expression

SELECT column from Table WHERE column ~ ' foo.* ';

The first NRecords

SELECT columns from table LIMIT 10;

Pagination

SELECT cols from table LIMIT, OFFSET 30;

Prepared statements

PREPARE preparedinsert (int, varchar) as  INSERT into TableName (Intcolumn, Charcolumn) VALUES ($, $); EXECUTE Preparedinsert (1, ' a '); EXECUTE Preparedinsert (2, ' B ');D eallocate Preparedinsert;

Create a Function

CREATE OR REPLACE FUNCTION Month (timestamp) RETURNS integer as  ' SELECT date_part (' month ', $ $):: 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 in table to username;

Perform a transaction

BEGIN TRANSACTION  UPDATE accounts SET balance + + 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;

http://www.petefreitag.com/cheatsheets/postgresql/

Turn: PostgreSQL Cheat Sheet