I have written a simple query that should show all table sizes of any given schema in human-readable format:
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'my_schema'
order by 2
When I run this query in PgAdmin, I get the following error:
ERROR: relation "my_table" does not exist
SQL state: 42P01
How can this error even be possible? I have not changed the information_schema at all and, if a relation doesn't exist in the first place, why would it be in the information_schema ? Any idea how this could happen?
Best Answer
The only reason I can think of, is if
my_schema
is not part of yoursearch_path
.As you pass a table name without schema qualifying it to the function
pg_relation_size()
the table is searched in the default search_path. If it's not found, you'll get that error.Use a fully qualified name instead: