Show the size of every table in every database in Redshift

I was getting annoyed with being unable to easily see the size of all of my tables in a Redshift cluster. This annoyance was compounded by the fact that I have several databases with identically named tables.

I didn't see a solution to this anywhere else on the internet. In case anyone else needs it, this query will return the Database Name, Table Name, and Table size in MB and GB.

SELECT m.mb,  
       cast(m.mb as float) / 1000 AS GB,
       m.name,
       m.tbl,
       m.db_id,
       TRIM(pg_database.datname) AS db
FROM (SELECT tbl,  
             mb,
             name,
             db_id
      FROM (SELECT tbl,
                   COUNT(*) AS mb
            FROM stv_blocklist
            GROUP BY tbl) t
        JOIN stv_tbl_perm ON t.tbl = stv_tbl_perm.id) m
  JOIN pg_database ON pg_database.oid = m.db_id

If you want to sanity check this, compare your results with those returned for the queries Amazon provides

Other random redshift notes:

##Copy from s3##
copy time  
from 's3://mybucket/data/timerows.lzo'  
credentials '<aws-auth-args>'  
lzop  
delimiter '|';

##Batched insert##
insert into category_stage values  
(default, default, default, default),
(20, default, 'Country', default),
(21, 'Concerts', 'Rock', default);


##New tables from existing data##
insert into category_stage  
(select * from category);

The following example creates CATEGORY_STAGE as a copy of CATEGORY and inserts all of the rows in CATEGORY into CATEGORY_STAGE.

create table category_stage as  
select * from category;

##Show size of all tables##
SELECT m.mb,  
       m.mb / 1000 as GB,
       m.name,
       m.tbl,
       m.db_id,
       TRIM(pg_database.datname) AS db
FROM (SELECT tbl,  
             mb,
             name,
             db_id
      FROM (SELECT tbl,
                   COUNT(*) AS mb
            FROM stv_blocklist
            GROUP BY tbl) t
        JOIN stv_tbl_perm ON t.tbl = stv_tbl_perm.id) m
  JOIN pg_database ON pg_database.oid = m.db_id

##Show tables##
  select distinct(tablename) from pg_table_def where schemaname = 'public';


##Show columns from table##
SELECT *  
FROM pg_table_def  
WHERE tablename = 'table_name'  
AND schemaname = 'public';

##UDF##
create function f_hello (a text)  
  returns text
stable  
as $$  
  return "Hello %s" % a
$$ language plpythonu
###See misbehaving locks###
select table_id, last_update, lock_owner, lock_owner_pid, lock_status  
from stv_locks;

#kill them
select pg_terminate_backend(pid)