Hello
On 2020-09-30 15:54, luis.roberto@xxxxxxxxxxxxxxx wrote:
De: "Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx>
Para: "luis.roberto" <luis.roberto@xxxxxxxxxxxxxxx>
Cc: "pgsql-general" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Enviadas: Quarta-feira, 30 de setembro de 2020 10:46:39
Assunto: Re: Table sizes
Hello
On 2020-09-30 14:11, luis.roberto@xxxxxxxxxxxxxxx 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
