Re: Table sizes

Hello

On 2020-09-30 15:54, luis(dot)roberto(at)siscobra(dot)com(dot)br wrote:
>> De: "Charles Clavadetscher" <clavadetscher(at)swisspug(dot)org>
>> Para: "luis.roberto" <luis(dot)roberto(at)siscobra(dot)com(dot)br>
>> Cc: "pgsql-general" <pgsql-general(at)lists(dot)postgresql(dot)org>
>> Enviadas: Quarta-feira, 30 de setembro de 2020 10:46:39
>> Assunto: Re: Table sizes
>>
>> Hello
>>
>> On 2020-09-30 14:11, luis(dot)roberto(at)siscobra(dot)com(dot)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