Bruno Baguette wrote:
I works on a PostgreSQL 8.0.4 database, and I would like to known the
size (in bytes, Mbytes or anything else) of that database, for each
table.
You've already got several replies. Know that the PG documentation is
excellent. If you visit www.postgresql.org, you will quickly find:
http://www.postgresql.org/docs/8.0/interactive/diskusage.html
The following won't help you, but just for completeness, in PG 8.1,
dbsize functionality is built in, so you can just do:
select tablename, pg_size_pretty(pg_total_relation_size(tablename)) as size
from pg_tables where schemaname in ('public') order by
pg_total_relation_size(tablename) desc;
select pg_size_pretty(pg_database_size(current_database())) AS TOTAL;
Also, replace or add to 'public' as required by the schemas you use.
The sizes reported include the indexes for each table.
The total includes not just your tables but the system tables also.
Unless you have collected lots of statistics on the database, the system
tables (pg_*) will probably only take up a few MB of space.
Note that pg_size_pretty() _rounds_ the raw byte numbers, so the pretty
results may understate the space requirements by half a unit.
If you want to exclude the system tables from the total database size,
then instead of pg_database_size(), you could use:
select pg_size_pretty(cast (sum(pg_total_relation_size(tablename)) as
bigint)) as size
from pg_tables where schemaname in ('public');
Kevin Murphy
