[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Table sizes
From: Charles Clavadetscher <clavadetscher () swisspug ! org>
Date: 2020-09-30 14:02:21
Message-ID: 46ba5e4effbac39b4cabec56d3250db2 () swisspug ! org
[Download RAW message or body]
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic