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;
===========================
Using PostgreSQL on FreeBSD
===========================
Find enum types::
dbtest=> SELECT pg_type.typname AS enumtype,
dbtest-> pg_enum.enumlabel AS enumlabel
dbtest-> FROM pg_type
dbtest-> JOIN pg_enum
dbtest-> ON pg_enum.enumtypid = pg_type.oid;
enumtype | enumlabel
--------------+------------
cnxrole_type | author
cnxrole_type | maintainer
cnxrole_type | copyright
(3 rows)
Setting up postgres on FreeBSD
------------------------------
::
$ make install clean
$ /usr/local/bin/postgres -D /usr/local/pgsql/data
for some reason everything gets put into /usr/local/pgsql/data - the config
files and so on.
Configuration and users
-----------------------
* postgresql.conf
For local service::
listen_addresses = 'localhost'
and in pg_hba.conf::
host all all 127.0.0.1/32 md5
We change ``trust`` to ``md5`` so that it exxpects user/password
$ createuser -sdrP test1
Enter password for new role:
Enter it again:
pgsql$ sudo su - pgsql
$ /usr/local/bin/createdb dbtest -O test1 encoding=UNICODE
$
$ psql -U test1 -d dbtest
...
dbtest=# CREATE USER repo WITH PASSWORD 'repopass';
CREATE ROLE
dbtest=# GRANT ALL PRIVILEGES ON DATABASE dbtest to repo;
GRANT
pgsql$ psql -h 127.0.0.1 -U repo -d dbtest
Password for user repo:
psql (9.2.1)
Type "help" for help.
dbtest=>
(NB localhost will try to use Unix domain sockets so use 127.0.0.1)
For network server::
listen_addresses = '*'
Logging
-------
Normally we want to ::
log_destination = 'syslog'
logging_collector = off
For development purposes we usually want to watch the SQL fly past::
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'