Firstly, thank you for any future help!
Onto my issue: I'm trying to get the size of all tables in my Greenplum database - Simple. However, there are quite a few partitioned tables, and I want their total size, not the independent child size. So I'm using the following query to do this:
select * from (
select schemaname as "Schema Name"
,tablename as "Table Name"
,cast(pg_relation_size(schemaname||'.'||tablename) as bigint ) / 1024 / 1024 as "Table Size (MB)"
from pg_tables
where schemaname||'.'||tablename not in (select schemaname||'.'||partitiontablename from pg_partitions)
and schemaname||'.'||tablename not in (select distinct schemaname||'.'||tablename from pg_partitions )
union all
select schemaname as "Schema Name"
,tablename as "Table Name"
,cast(sum(pg_relation_size(schemaname||'.'||partitiontablename)) as bigint ) / 1024 / 1024 as "Table Size (MB)"
from pg_partitions
group by 1, 2
) as union_join
where "Table Size (MB)" > '50'
order by 3 desc
;
However, it keeps throwing an error saying, "schema xyz doesn't exist." And... it's right, it doesn't... So I pulled out the following bit of code, added in a where clause, and got it to run:
select schemaname
,tablename
,cast(sum(pg_relation_size(schemaname||'.'||partitiontablename)) as bigint ) / 1024 / 1024 as "Table Size (MB)"
from pg_partitions
where schemaname != 'XYZ'
group by 1, 2
limit 1
;
And this works!! If I change the code to include only 'XYZ' instead, it then throws the same error as before, saying, "schema xyz doesn't exist." and around we go...
Now I want it to run for all schemas, so I cannot just exclude XYZ (and there are multiples with capitals, sadly). I'm assuming the error has something to do with the following part:
pg_relation_size(schemaname||'.'||partitiontablename)
but honestly I cannot figure out why, or even if it is this bit. Googling around hasn't yielded anything, other than basics of working with capitals (double quotes, yada yada), and I'm now banging my head against a wall slowly loosing my mind...
Any and all help is hugely appreciated.
Try properly escaping the identifiers:
pg_relation_size(format('%I.%I, schemaname, tablename)::regclass)