From: | luis(dot)roberto(at)siscobra(dot)com(dot)br |
---|---|
To: | Charles Clavadetscher <clavadetscher(at)swisspug(dot)org> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Table sizes |
Date: | 2020-09-30 13:54:32 |
Message-ID: | 56108176.54473966.1601474072988.JavaMail.zimbra@siscobra.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
+---------------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ 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).
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-09-30 13:59:03 | Re: Table sizes |
Previous Message | Charles Clavadetscher | 2020-09-30 13:46:39 | Re: Table sizes |