PostgreSQL Commands

Useful SQL for DBAs

List all tables in the current database:

=> SELECT tablename FROM pg_tables where tablename not like 'pg_%';

Limit rows returned as in where rownum <= 10

=> select tablename from pg_tables limit 10;

List tables in one schema

=> select tablename from pg_tables where tableowner='qedit';

List running queries

=> select * from pg_stat_activity;

List database activity

=> select * from pg_stat_database;

List table IO

=> select * from pg_statio_all_tables;

List largest tables

=> SELECT relname, relpages * 8 as kilobytes FROM pg_class

=> WHERE relname not like 'pg_%' order by relpages DESC;

Show row counts for all tables

=> ANALYZE;

=> SELECT n.nspname, relname, reltuples
FROM pg_class c, pg_namespace n
WHERE c.relnamespace=n.oid
AND relkind='r'
AND NOT n.nspname ~ '^pg_'
ORDER BY 1,2;

See Also

MySQL and Postgres command equivalents (mysql vs psql)

Subject