finding the count of all table rows in a schema in postgres

Asked Dec 18 '14 at 06:22

Active Dec 18 '14 at 06:58

Viewed 3,915 times

I used the following command to obtain the count of each table rows in a schema in oracle 11g. To do the same in postgres what changes should be made? D6 is the schema name

Query:

select
table_name,
to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) D6 from '||table_name))
,'/ROWSET/ROW/D6')) count
from user_tables;


Error:

ERROR:  relation "user_tables" does not exist
LINE 8: from user_tables;
             ^


********** Error **********

ERROR: relation "user_tables" does not exist
SQL state: 42P01
Character: 163

The following error is displayed. What changes shall be made?

edited Dec 18 '14 at 06:58

asked Dec 18 '14 at 06:22

Reenu Rahman

possible solution `postgresql` (http://stackoverflow.com/q/13931494/2749470) – Bhargav Modi Dec 18 '14 at 06:27
@Bhargav I want the number of rows in each table in the schema .this query returns the number of tables in a schema Table_name: User_history Count:415 etc.. – Reenu Rahman Dec 18 '14 at 06:34
`SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;` – Bhargav Modi Dec 18 '14 at 06:59
`n_live_tup` is an estimate. That might or might not matter. – Mike Sherrill 'Cat Recall' Dec 20 '14 at 12:33
@Reenu, this seems to be the same as http://stackoverflow.com/q/2596670 - I hope this is helpful to you. Good luck. – Dawood ibn Kareem Jan 16 '15 at 23:45
yes thank you @David found the same answer some time back.. thanks for spending your time :) – Reenu Rahman Jan 20 '15 at 08:17
@ReenuRahman I didn't really spend any time on it. I just wish I had seen your question when you first posted it, instead of a month too late. – Dawood ibn Kareem Jan 20 '15 at 08:19

finding the count of all table rows in a schema in postgres

0 Answers0