======================
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'