Notes

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Applications - Databases - PostgreSQL - Notes - Miscellaneous======

Show all databases:
<code postgresql>
\l
</code>

Connect to a database:
<code postgresql>
\c DATABASENAME
</code>

Show tables with size:
<code>
 \dt+
</code>

Show column details (describe table):
<code postgresql>
\d+ TABLENAME
</code>

Show schemas:
<code postgresql>
\dn
</code>


=====Show table sizes=====

Show the 20 biggest tables with total size from big to small:
<code postgresql>
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;
</code>
Source:[[https://wiki.postgresql.org/wiki/Disk_Usage|PostgreSQL Wiki - Disk Usage]]


=====Show what is running=====

Show what is running now:
<code postgresql>
SELECT * FROM pg_stat_activity WHERE state != 'idle';
</code>
Source: [[https://www.postgresql.org/docs/10/monitoring-stats.html|Documentation -PostgreSQL 10 - 28.2. The Statistics Collector]]


=====Show all tables in all schemas of current database=====

<code postgresql>
SELECT schemaname,tablename 
FROM pg_catalog.pg_tables 
WHERE schemaname != 'pg_catalog' 
AND schemaname != 'information_schema' 
ORDER BY schemaname,tablename;
</code>
Source: [[https://kb.objectrocket.com/postgresql/connect-to-postgresql-and-show-the-table-schema-967|ObjectRocket - Connect to PostgreSQL and Show the Table Schema]]