Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    17

    Unanswered: DBA_SEGMENTS & DBA_TABLES substitute in Postgres

    Hi,

    I want to get the size of tables in postgres. Is there a way to do it as we do in oracle using DBA_SEGMENTS.

    one option is using :

    SELECT pg_size_pretty(pg_relation_size('table_name'));

    but this function allows only one table at a time but my requirement is to get the sum of sizes of all tables which I can pass in where clause.

    Also, is there any substitute for DBA_TABLES where i can get the count of all tables ?

    Thanks,
    Zubi

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by zubi6050 View Post
    but this function allows only one table at a time but my requirement is to get the sum of sizes of all tables which I can pass in where clause.
    Do you mean something like pg_database_size()?
    PostgreSQL: Documentation: 9.3: System Administration Functions

    But you can also sum this for all tables:
    Code:
    SELECT pg_relation_size(tablename)
    from pg_tables;
    Also, is there any substitute for DBA_TABLES where i can get the count of all tables ?
    PostgreSQL: Documentation: 9.3: pg_tables
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    May 2013
    Posts
    17
    Thanks Shammat.

    I tried using this code:

    SELECT pg_relation_size(tablename)
    from pg_tables;

    but it throws me an error as it expects the argument and not the column name. And if i put quotes like this :

    SELECT pg_relation_size('tablename')
    from pg_tables;

    'tablename' becomes the relation name and not the column of pg_tables which again throws an error that "relation tablename does not exist.
    I want to do something like this in postgres as i can do in oracle:


    SELECT( ((SUM ( BYTES))/1024)/1024)/1024 AS size_gb FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('table1','table2','table3','table4','table5') AND OWNER = 'my_schema';

    Is it possible?

    Also by count of tables I mean is there a table in postgres which gives the total count of rows in all tables?

    Is this ok to use:

    SELECT schemaname,relname,n_live_tup
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;


    Thanks
    Zubi..

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Sorry, you need to cast tablename to a text

    Code:
    SELECT pg_relation_size(tablename::text)
    from pg_tables
    where schemaname = 'public';
    and please format your SQL code properly.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •