Re: Table sizes - Mailing list pgsql-general
From | Charles Clavadetscher |
---|---|
Subject | Re: Table sizes |
Date | |
Msg-id | 46ba5e4effbac39b4cabec56d3250db2@swisspug.org Whole thread Raw |
In response to | Re: Table sizes (luis.roberto@siscobra.com.br) |
List | pgsql-general |
Hello On 2020-09-30 15:54, luis.roberto@siscobra.com.br wrote: >> De: "Charles Clavadetscher" <clavadetscher@swisspug.org> >> Para: "luis.roberto" <luis.roberto@siscobra.com.br> >> Cc: "pgsql-general" <pgsql-general@lists.postgresql.org> >> Enviadas: Quarta-feira, 30 de setembro de 2020 10:46:39 >> Assunto: Re: Table sizes >> >> Hello >> >> On 2020-09-30 14:11, luis.roberto@siscobra.com.br wrote: >>> Hi! >>> >>> I'm trying to use this query to get table sizes, however I'm >> getting a >>> strange error: >>> >>> select tablename,pg_relation_size(tablename::text) >>> from pg_tables; >>> >>> In PG 13: >>> >>> SQL Error [42P01]: ERROR: relation "sql_implementation_info" does >> not >>> exist >>> >>> In PG 12: >>> >>> SQL Error [42P01]: ERROR: relation "sql_parts" does not exist >> >> Try like this: >> >> select schemaname, >> tablename, >> pg_relation_size((schemaname || '.' || '"' || tablename || >> '"')::regclass) >> from pg_tables; >> >> You need to schema qualify the tables. Additionally, if you happen >> to >> have table names that have a mix of capital and non capital letters >> or >> contain other characters that might be problematic, you need to >> enclose >> the table name in double quotes. >> >> Regards >> Charles >> >> -- >> Charles Clavadetscher >> Swiss PostgreSQL Users Group >> Treasurer >> Spitzackerstrasse 9 >> CH - 8057 Zürich >> >> http://www.swisspug.org >> >> +---------------------------+ >> | ____ ______ ___ | >> | / )/ \/ \ | >> | ( / __ _\ ) | >> | \ (/ o) ( o) ) | >> | \_ (_ ) \ ) _/ | >> | \ /\_/ \)/ | >> | \/ <//| |\\> | >> | _| | | >> | \|_/ | >> | | >> | Swiss PostgreSQL | >> | Users Group | >> | | >> +---------------------------+ > > ------------------------- > > Thanks, this worked. > > I wonder though, why calling pg_relation_size('users') work (I don't > need to specify the schema). Glad to hear that. The other problem probably depend on your search_path. You can look at it using (in a psql shell): show search_path; Tables that are in your search_path don't need to be schema qualified, those that aren't need it. If you need to, you can change your search_path. set search_path to ...; To change it permanently you can use ALTER ROLE: ALTER ROLE <yourrole> SET search_path=... ; Have a look for more information at https://www.postgresql.org/docs/current/ddl-schemas.html (chapter 5.9.3). Regards Charles
pgsql-general by date: