From: | Thomas Markus <t(dot)markus(at)proventis(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Returning schema name with table name |
Date: | 2008-11-24 07:19:00 |
Message-ID: | 492A5564.2060501@proventis.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
my standard query (adapted to 1mb size) is:
select
t.spcname as "tablespace"
, pg_get_userbyid(c.relowner) as "owner"
, n.nspname as "schema"
, relname::text as "name"
, pg_size_pretty(pg_total_relation_size(c.oid))::text as "total size"
, case
when c.relkind='i' then 'index'
when c.relkind='t' then 'toast'
when c.relkind='r' then 'table'
when c.relkind='v' then 'view'
when c.relkind='c' then 'composite type'
when c.relkind='S' then 'sequence'
else c.relkind::text
end as "type"
from
pg_class c
left join pg_namespace n on n.oid = c.relnamespace
left join pg_tablespace t on t.oid = c.reltablespace
where
(pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
c.relkind desc, pg_total_relation_size(c.oid) desc
Andrus schrieb:
> SELECT oid, relname::char(35) as Table_Name,
> pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as
> Total_Table_Size
> FROM pg_class
> where pg_total_relation_size(oid)/(1024*1024)>0
> ORDER BY pg_total_relation_size(oid) desc
>
> returns table names with size greater than 1 MB
>
> How to modify this so that schema name is also returned?
> I have lot of tables with same name and thus this output is difficult
> to understand.
> pg_class seems not contain schema names.
>
> Andrus.
>
>
--
Thomas Markus
====================================================
proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | t(dot)markus(at)proventis(dot)net
-----------------------------------------------------------------
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-----------------------------------------------------------------
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html
====================================================
Attachment | Content-Type | Size |
---|---|---|
t_markus.vcf | text/x-vcard | 255 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Abdul Rahman | 2008-11-24 07:51:46 | PgAgent Job Scehduler is NOT running |
Previous Message | Ciprian Dorin Craciun | 2008-11-24 05:27:05 | Re: Using Postgres to store high volume streams of sensor readings |