3

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?

Improve this question
2
  • postgres has like all rdms a lot of bugs, report it
    – nbk
    Feb 13, 2021 at 21:39
  • 2
    @nbk: while I agree that no software is free of bugs, I wouldn't qualify Postgres as having "lots of them". And this is probably not one to begin with, rather a user error.
    – a_horse_with_no_name
    Feb 13, 2021 at 22:08

1 Answer 1

Reset to default
5

The only reason I can think of, is if my_schema is not part of your search_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:

pg_relation_size(format('%I.%I', table_schema, table_name))
Improve this answer
3
  • i think it is an error as my_table exists in the table schema, but the error says that the table doesn't exist
    – nbk
    Feb 13, 2021 at 22:11
  • 2
    @nbk: no it's not an error. my_table references a table in the default schema/search_path (e.g. public) . my_schema.my_table references the table in the specific schema.
    – a_horse_with_no_name
    Feb 13, 2021 at 22:17
  • I would phrase this even stronger because I think the default search_path is <username>, public, so unless OP fiddled with the search_path, we know that my_schema is not in the search_path.
    – AndreKR
    Feb 14, 2021 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.