How to estimate the size of one column in a Postgres table?

Question

There is a column of type text in a table in Postgres 9.1. I'd like to know the impact of just that column on the disk space needed. It doesn't need to be precise, but I'd like to get an idea if that column is responsible for 20%/30%/... of the disk space consumed by the database.

I know pg_relation_size, but it only operates at table level.

I have many databases with this same schema. I dumped a smaller one and cut out the column with grep and cut and compared the size of the plain text dumps. But this is not necessarily a good indicator of space requirements in the live db, and it's also more difficult to do that for large databases.

Answer 1

select     sum(pg_column_size(the_text_column)) as total_size,     avg(pg_column_size(the_text_column)) as average_size,     sum(pg_column_size(the_text_column)) * 100.0 / pg_relation_size('t') as percentage from t; 

Answer 2

Slight improvement on the accepted answer: pretty print the size and use pg_total_relation_size to be more accurate.

select     pg_size_pretty(sum(pg_column_size(column_name))) as total_size,     pg_size_pretty(avg(pg_column_size(column_name))) as average_size,     sum(pg_column_size(column_name)) * 100.0 / pg_total_relation_size('table_name') as percentage from table_name;