Обсуждение: Table sizes

W dniu 2020-09-30 o 14:11, luis.roberto@siscobra.com.br pisze:
> 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
>
You need to use full tablenames, using schemaname of pg_tables too.

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         |
|                           |
+---------------------------+

On 9/30/20 6:54 AM, luis.roberto@siscobra.com.br wrote:

> ------------------------------------------------------------------------
> 
> Thanks, this worked.
> 
> I wonder though, why calling pg_relation_size('users') work (I don't 
> need to specify the schema).

Because it is in the search_path. In psql do:

show search_path;

It will show you the schemas Postgres will search for a table name 
without using a schema qualification.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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