**********
PostgreSQL
**********
.. toctree::
    :maxdepth: 2

    setup
    plpython

.. highlight:: bash

PostgreSQL 9.2 on Ubuntu 12.04 (`source <http://askubuntu.com/questions/186610/how-do-i-upgrade-to-postgres-9-2>`__)::

    sudo add-apt-repository ppa:pitti/postgresql 
    sudo apt-get update
    sudo apt-get install postgresql-9.2


Allow connections via socket and MD5 password
=============================================
sudo vim /etc/postgresql/9.2/main/pg_hba.conf::

    # md5-based auth instead of peer auth
    #local   all             all                                     peer
    local   all             all                                     md5

Reload server::

    service postgresql reload


Allow User Access as postgres
=============================
This should work (as normal user)::

    cat <<EOF >> ~/.pgpass
    # hostname:port:database:username:password
    localhost:*:postgres:postgres:postgres_password
    EOF
    psql postgres postgres

But it does not by default. Set password for `postgres`::

    sudo -Hnu postgres -- psql -c "alter user postgres with password 'postgres_password'"

Allow local login::

    cat <<EOF >> /etc/postgresql/9.3/main/pg_hba.conf
    local   all             all                                     md5
    EOF

.. note::
    This will not work if you leave the line::

        local   all             postgres                                peer

Use `.pgpass` of postgres user, so `sudo -Hnu postgres -- psql` still works::

    cat <<EOF >> /var/lib/postgresql/.pgpass
    # hostname:port:database:username:password
    localhost:*:postgres:postgres:postgres_password
    EOF

Test::

    psql postgres postgres -c "SELECT 'world' AS hello"
    sudo -Hnu postgres -- psql -c "SELECT 'world' AS hello"


Example database
================
Connect as user *postgres* (admin)::

    sudo -u postgres psql

Create user and database:

.. code-block:: sql

    CREATE USER sample WITH PASSWORD 'sample';
    CREATE DATABASE users WITH OWNER sample;
    GRANT ALL PRIVILEGES ON DATABASE users TO sample;

Add line to your `.pgpass`_ file::

    echo '# hostname:port:database:username:password' >> ~/.pgpass
    echo '*:*:users:sample:sample' >> ~/.pgpass
    cat ~/.pgpass

.. _.pgpass: http://www.postgresql.org/docs/9.3/static/libpq-pgpass.html

Try it::

    psql users sample

.. todo:: publish repo with example db, link here

Clean up:

.. code-block:: sql

    sudo -u postgres psql <<'EOF'
    DROP DATABASE users;
    DROP USER sample;
    EOF


Clean Your Room!
================
::

    sudo -Hu postgres vacuumdb -az


Create/Drop User and DB via CLI Tools
=====================================
Create::

    sudo -Hnu postgres createuser foo --encrypted --createdb --pwprompt
    echo 'localhost:*:*:foo:<pass_here>' >> ~/.pgpass
    vim ~/.pgpass
    sudo -Hnu postgres createdb --owner foo foo

Drop::

    sudo -Hnu postgres dropdb foo
    sudo -Hnu postgres dropuser foo


Text Similarity
===============
Install and enable::

    # check version
    sudo -iu postgres psql -c "select version()"
    wajig install postgresql-contrib-9.4
    sudo -iu postgres psql -c "create extension pg_trgm" DBNAME

Usage::

    SELECT similarity('foo bar baz', 'foo bar baz qux');

Docs: http://www.postgresql.org/docs/9.1/static/pgtrgm.html


Minor Version Upgrade
=====================
Here's the log from 9.4 to 9.5::

    wajig install postgresql-9.5
    # backups
    cp -r /var/lib/postgresql/9.4/main/ /var/lib/postgresql/9.4/main.bak
    cp -r /var/lib/postgresql/9.5/main/ /var/lib/postgresql/9.5/main.bak

    service postgresql stop
    pg_dropcluster 9.5 main
    pg_upgradecluster 9.4 main
    
    # verify stuff

    # remove old stuff
    pg_dropcluster 9.4 main
    wajig purge postgresql-9.4

    # verify again


Minimal Upsert Example (9.5)
============================
.. code-block:: sql

    CREATE TEMP TABLE tmp_target (
      key   TEXT UNIQUE,
      value TEXT
    )
    ;

    INSERT INTO tmp_target VALUES
      ('foo', 'Foo'),
      ('bar', 'Bar')
    ;

    CREATE TEMP TABLE tmp_source (
      key   TEXT UNIQUE,
      value TEXT
    )
    ;

    TRUNCATE tmp_source;
    INSERT INTO tmp_source (key, value) VALUES
      ('foo', 'UPDATED Foo'),
      ('bar', 'Bar'),
      ('baz', 'Baz')
    ;

    INSERT INTO tmp_target (key, value)
        SELECT key, value FROM tmp_source
    ON CONFLICT (key)
      DO UPDATE SET value = EXCLUDED.value -- http://stackoverflow.com/a/35159684
    ;

    SELECT * FROM tmp_target;

    DROP TABLE tmp_target;
    DROP TABLE tmp_source;


Dump and Load Table via copy PSQL command
=========================================
::

    -- dump to file
    \copy foo TO '/tmp/foo.csv';

    -- create load table based on source table
    create table tmp_foo as select * from foo with no data;
    \copy tmp_foo from '/tmp/foo.csv';

    select * from tmp_foo;
    select * from foo;

    -- see Upsert above
    drop table tmp_foo;


Links
=====
- examples (outdated but cool) http://www.commandprompt.com/ppbook/booktown.sql
- Sequences http://www.neilconway.org/docs/sequences/
- environment variables http://www.postgresql.org/docs/9.2/static/libpq-envars.html
- psql script debug flags http://archives.postgresql.org/pgsql-hackers/2010-06/msg00350.php
- limit and total: http://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied-in-php-postgres
    - problem with many (> 500k) rows
    - maybe adapt the following strategy (to deliver query times < 0.5 sec):
        - if count(*) < 100000: select via count(*) over()
        - else: return count from table "counts (table_name, count, date)"
        - background job - nightly or parallel (maybe even on RO-replica)
- timing queries --> ``\timing``: http://dba.stackexchange.com/questions/3148/how-can-i-time-sql-queries-using-psql