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;