Re: Table sizes

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