postgresql -- an ORDBMS

As I learn more, I'll post more things that I should remember. ;)

  1. Show list of current connections:
    ps auxww | grep post[g]res:

    Monitoring from the shell seems to work best. The format is "postgres: user database host activity" -- from http://www.postgresql.org/docs/8.1/interactive/monitoring.html

    select * from pg_catalog.pg_stat_activity;

    currently failing my tests. More info on the pg_stat interface at: http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html

  2. List tables and indices by disk space used:
    SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

    from http://www.postgresql.org/docs/8.1/interactive/diskusage.html

  3. Variable substitution:
    \set var 3
    select * from test where test_column = :var

    from http://www.postgresql.org/docs/8.1/interactive/app-psql.html

  4. Row-level locking for updates:
    select * from :table where :testcondition for update;

    from http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html

  5. Temporary tables:
    create temporary table foo AS select * from bar;

    from http://www.postgresql.org/docs/8.1/static/sql-createtableas.html

  6. Create if not exist:
    if not exists ( select 1 from pg_class where relname = 'table_X' ) then
    create table tableX ...;
    end if;

    from http://www.varlena.com/GeneralBits/64.php

  7. What functions have been defined?:
    select * from pg_proc, pg_language
    where pg_proc.prolang = pg_language.oid and pg_language.lanname = 'plpgsql';

    from http://archives.postgresql.org/pgsql-de-allgemein/2004-09/msg00001.php


Is there some project in particular that you are going to use postgres for? - Nathan
I've used it for personal data for a while. I'm now ramping up usage for collecting some web-available data on my colo.