SQL snippets for PostgreSQL

Contents: [Dobrica Pavlinušić's random unstructured stuff]
• Dobrica Pavlinušić's random unstructured stuff (Size of transaction log) 
• Dobrica Pavlinušić's random unstructured stuff (Size of tables in current database) 
• Dobrica Pavlinušić's random unstructured stuff (Number of rows in all tables) 
• Dobrica Pavlinušić's random unstructured stuff (Script to display database size) 
• Dobrica Pavlinušić's random unstructured stuff (all databases) 
• Dobrica Pavlinušić's random unstructured stuff (all databases, grouped by user) 
• Dobrica Pavlinušić's random unstructured stuff (Active locks) 
• Dobrica Pavlinušić's random unstructured stuff (Graphics) 
• Dobrica Pavlinušić's random unstructured stuff (Current query activity) 
• Dobrica Pavlinušić's random unstructured stuff (Quickly convert database table to SQLite) 

Size of transaction log
select sum((pg_stat_file('pg_xlog/' || file)).size)
from pg_ls_dir('pg_xlog') as file
where file ~ '^[0-9A-F]';

Size of tables in current database
select relname,pg_relation_size(oid) as size
from pg_class
where relkind = 'r' and relname not like 'pg_%' order by size desc;

Number of rows in all tables
http://stackoverflow.com/questions/2596670/how‑do‑you‑find‑the‑row‑count‑for‑all‑your‑tables‑in‑postgres 
SELECT 
  nspname AS schemaname,relname,reltuples::int
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

Script to display database size
Script: pg_size 
all databases
$ pg_size

all databases, grouped by user
$ pg_size -u

Active locks
select * from pg_locks where pid not in (select procpid from pg_stat_activity);

Graphics
Taken from http://lca2007.linux.org.au/att_data/Miniconfs(2f)PostgreSQL/attachments/advanced.pdf 
# CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

# select d, count(*),
       array_to_string(array_accum('+'::text),'') as graph
       from hits group by 1 order by 1 asc;
     d       | count | graph
------------+-------+----------
 2007-01-13 |      8 | ++++++++
 2007-01-14 |      7 | +++++++
 2007-01-15 |      4 | ++++
(3 rows)

Current query activity
select datid,datname,procpid,current_query,query_start from pg_stat_activity where current_query not like '<IDLE>' ;

To actually see queries you also have to edit postgresql.conf to include:
stats_command_string = yes

Quickly convert database table to SQLite
pg_dump --inserts --no-owner --no-privileges --table hr_edu_orgs a3c | grep -v ^SET | sqlite3 var/a3c.sqlite