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

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).

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