select
table_schema,
table_name,
pg_size_pretty(size) as tsize,
rowcount
from (
SELECT
table_schema,
table_name,
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size,
(select reltuples from pg_class pc where pc.relname = t1.table_name ) as rowcount
FROM information_schema.tables t1
) t
order by size desc;
查询所有模式\表名称\字段信息, 排除分区表
SELECT
pd.schemaname as 模式名,
pc.relname as 表名,
pa.attname AS 列名,
format_type ( pa.atttypid, pa.atttypmod ) AS 类型,
(CASE WHEN pa.attlen > 0 THEN pa.attlen ELSE pa.atttypmod - 4 END ) AS 长度,
pa.attnotnull AS 可空,
(
CASE WHEN ( SELECT COUNT(*) FROM pg_constraint WHERE conrelid = pa.attrelid AND conkey[1]= attnum AND contype = 'p' ) > 0 THEN
TRUE ELSE FALSE
END
) AS 主键,
col_description ( pa.attrelid, pa.attnum ) AS 描述
FROM
pg_class AS pc,
pg_attribute AS pa,
pg_tables as pd
WHERE
pd.tablename = pc.relname
AND pa.attrelid = pc.oid
AND pa.attnum > 0
and pc.relname not in
(
select c.relname
from pg_class c
join pg_inherits i on i.inhrelid = c. oid
join pg_class d on d.oid = i.inhparent
)
查询所有分群表
select c.relname
from pg_class c
join pg_inherits i on i.inhrelid = c. oid
join pg_class d on d.oid = i.inhparent
where d.relname = '分区表';