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