How do you find the row count for all your tables in Postgres

Question

I m looking for a way to find the row count for all my tables in Postgres   I know I can do this one table at a time with   SELECT count    FROM table name    but I d like to see the row count for all the tables and then order by that to get an idea of how big all my tables are

User · Answer

You Can use this query to generate all tablenames with their counts

select ' select  '''|| tablename  ||''', count(*) from ' || tablename ||' 
union' from pg_tables where schemaname='public';

the result from the above query will be

select  'dim_date', count(*) from dim_date union 
select  'dim_store', count(*) from dim_store union
select  'dim_product', count(*) from dim_product union
select  'dim_employee', count(*) from dim_employee union

You'll need to remove the last union and add the semicolon at the end !!

select  'dim_date', count(*) from dim_date union 
select  'dim_store', count(*) from dim_store union
select  'dim_product', count(*) from dim_product union
select  'dim_employee', count(*) from dim_employee  **;**

RUN !!!

User · Answer

I like Daniel V  rit   s answer  But when you can t use a CREATE statement you can either use a bash solution or  if you re a windows user  a powershell one     You don t need this if you have pgpass conf  env PGPASSWORD    userpass     Get table list  tables    amp   C  Program Files PostgreSQL 9 4 bin psql exe  -U user -w -d dbname -At -c  select table name from information schema tables where table type  BASE TABLE  AND table schema  schema1    foreach   table in  tables         amp   C  path to postresql bin psql exe  -U root -w -d dbname -At -c  select   table   count    from  table

User · Answer

Simple Two Steps    Note   No need to change anything - just copy paste   1  create function  create function  cnt rows schema text  tablename text  returns integer as  body  declare   result integer    query varchar  begin   query     SELECT count 1  FROM      schema           tablename    execute query into result    return result  end   body  language plpgsql    2  Run this query to get rows count for all the tables  select sum cnt rows  as total no of rows from  select    cnt rows table schema  table name  from information schema tables where    table schema not in   pg catalog    information schema      and table type  BASE TABLE   as subq    or  To get rows counts tablewise  select   table schema    table name     cnt rows table schema  table name  from information schema tables where    table schema not in   pg catalog    information schema      and table type  BASE TABLE  order by 3 desc

User · Answer

This worked for me      SELECT schemaname relname n live tup FROM pg stat user tables ORDER BY   n live tup DESC

User · Answer

If you re in the psql shell  using  gexec allows you to execute the syntax described in syed s answer and Aur s answer without manual edits in an external text editor  with x  y  as       select          select count                   tablename               as  quot tablename quot  from             tablename          from pg tables     where schemaname  public    select     string agg y   union all    chr 10        order by tablename  from x  gexec  Note  string agg   is used both to delimit union all between statements and to smush the separated datarows into a single unit to be passed into the buffer    gexec Sends the current query buffer to the server  then treats each column of each  row of the query s output  if any  as a SQL statement to be executed

User · Answer

Not sure if an answer in bash is acceptable to you  but FWIW     PGCOMMAND   psql -h localhost -U fred -d mydb -At -c                SELECT   table name             FROM     information schema tables             WHERE    table type  BASE TABLE              AND      table schema  public                  TABLENAMES   export PGPASSWORD test  eval   PGCOMMAND    for TABLENAME in  TABLENAMES  do     PGCOMMAND   psql -h localhost -U fred -d mydb -At -c                    SELECT     TABLENAME                            count                     FROM      TABLENAME                         eval   PGCOMMAND  done

User · Answer

Here is a much simpler way  tables  quot   echo   dt    psql -U  quot   PGUSER  quot    tail -n  4   head -n-2   tr -d       cut -d     -f2  quot  for table in  tables  do printf  quot  s   s n quot   quot  table quot   quot   echo  quot SELECT COUNT    FROM  table  quot    psql -U  quot   PGUSER  quot    tail -n  3   head -n-2   tr -d      quot  done  output should look like this auth group  0 auth group permissions  0 auth permission  36 auth user  2 auth user groups  0 auth user user permissions  0 authtoken token  2 django admin log  0 django content type  9 django migrations  22 django session  0 mydata table1  9011 mydata table2  3499  you can update the psql -U  quot   PGUSER  quot  portion as needed to access your database note that the head -n-2 syntax may not work in macOS  you could probably just use a different implementation there Tested on psql  PostgreSQL  11 2 under CentOS 7  if you want it sorted by table  then just wrap it with sort for table in  tables  do printf  quot  s   s n quot   quot  table quot   quot   echo  quot SELECT COUNT    FROM  table  quot    psql -U  quot   PGUSER  quot    tail -n  3   head -n-2   tr -d      quot  done   sort -k 2 2nr  output  mydata table1  9011 mydata table2  3499 auth permission  36 django migrations  22 django content type  9 authtoken token  2 auth user  2 auth group  0 auth group permissions  0 auth user groups  0 auth user user permissions  0 django admin log  0 django session  0

User · Answer

If you don t mind potentially stale data  you can access the same statistics used by the query optimizer   Something like   SELECT relname  n tup ins - n tup del as rowcount FROM pg stat all tables

User · Answer

The hacky  practical answer for people trying to evaluate which Heroku plan they need and can t wait for heroku s slow row counter to refresh   Basically you want to run  dt in psql  copy the results to your favorite text editor  it will look like this    public   auth group                       table   axrsosvelhutvw  public   auth group permissions           table   axrsosvelhutvw  public   auth permission                  table   axrsosvelhutvw  public   auth user                        table   axrsosvelhutvw  public   auth user groups                 table   axrsosvelhutvw  public   auth user user permissions       table   axrsosvelhutvw  public   background task                  table   axrsosvelhutvw  public   django admin log                 table   axrsosvelhutvw  public   django content type              table   axrsosvelhutvw  public   django migrations                table   axrsosvelhutvw  public   django session                   table   axrsosvelhutvw  public   exercises assignment             table   axrsosvelhutvw      then run a regex search and replace like this            s          s    table         to   select   1   count    from  1 union g   which will yield you something very similar to this   select  auth group   count    from auth group union select  auth group permissions   count    from auth group permissions union select  auth permission   count    from auth permission union select  auth user   count    from auth user union select  auth user groups   count    from auth user groups union select  auth user user permissions   count    from auth user user permissions union select  background task   count    from background task union select  django admin log   count    from django admin log union select  django content type   count    from django content type union select  django migrations   count    from django migrations union select  django session   count    from django session      You ll need to remove the last union and add the semicolon at the end manually   Run it in psql and you re done                column               count -------------------------------- -------  auth group permissions               0  auth user user permissions           0  django session                    1306  django content type                 17  auth user groups                   162  django admin log                  9106  django migrations                   19

User · Answer

There s three ways to get this sort of count  each with their own tradeoffs   If you want a true count  you have to execute the SELECT statement like the one you used against each table   This is because PostgreSQL keeps row visibility information in the row itself  not anywhere else  so any accurate count can only be relative to some transaction   You re getting a count of what that transaction sees at the point in time when it executes   You could automate this to run against every table in the database  but you probably don t need that level of accuracy or want to wait that long   The second approach notes that the statistics collector tracks roughly how many rows are  live   not deleted or obsoleted by later updates  at any time   This value can be off by a bit under heavy activity  but is generally a good estimate   SELECT schemaname relname n live tup    FROM pg stat user tables    ORDER BY n live tup DESC    That can also show you how many rows are dead  which is itself an interesting number to monitor   The third way is to note that the system ANALYZE command  which is executed by the autovacuum process regularly as of PostgreSQL 8 3 to update table statistics  also computes a row estimate   You can grab that one like this   SELECT    nspname AS schemaname relname reltuples FROM pg class C LEFT JOIN pg namespace N ON  N oid   C relnamespace  WHERE    nspname NOT IN   pg catalog    information schema   AND   relkind  r   ORDER BY reltuples DESC    Which of these queries is better to use is hard to say   Normally I make that decision based on whether there s more useful information I also want to use inside of pg class or inside of pg stat user tables   For basic counting purposes just to see how big things are in general  either should be accurate enough

User · Answer

Here is a solution that does not require functions to get an accurate count for each table   select table schema          table name           xpath   row cnt text     xml count   1   text  int as row count from     select table name  table schema            query to xml format  select count    as cnt from  I  I   table schema  table name   false  true      as xml count   from information schema tables   where table schema    public  -- lt  lt  change here for the schema you want   t   query to xml will run the passed SQL query and return an XML with the result  the row count for that table   The outer xpath   will then extract the count information from that xml and convert it to a number  The derived table is not really necessary  but makes the xpath   a bit easier to understand - otherwise the whole query to xml   would need to be passed to the xpath   function

User · Answer

To get estimates  see Greg Smith s answer   To get exact counts  the other answers so far are plagued with some issues  some of them serious  see below   Here s a version that s hopefully better   CREATE FUNCTION rowcount all schema name text default  public     RETURNS table table name text  cnt bigint  as    declare  table name text  begin   for table name in SELECT c relname FROM pg class c     JOIN pg namespace s ON  c relnamespace s oid      WHERE c relkind    r  AND s nspname schema name   LOOP     RETURN QUERY EXECUTE format  select cast  L as text  count    from  I  I          table name  schema name  table name     END LOOP  end    language plpgsql    It takes a schema name as parameter  or public if no parameter is given   To work with a specific list of schemas or a list coming from a query without modifying the function  it can be called from within a query like this   WITH rc schema name tbl  AS     select s n rowcount all s n  from  values   schema1     schema2    as s n    SELECT schema name  tbl    FROM rc    This produces a 3-columns output with the schema  the table and the rows count   Now here are some issues in the other answers that this function avoids    Table and schema names shouldn t be injected into executable SQL without being quoted  either with quote ident or with the more modern format   function with its  I format string  Otherwise some malicious person may name their table tablename DROP TABLE other table which is perfectly valid as a table name  Even without the SQL injection and funny characters problems  table name may exist in variants differing by case  If a table is named ABCD and another one abcd  the SELECT count    FROM    must use a quoted name otherwise it will skip ABCD and count abcd twice  The  I of format does this automatically  information schema tables lists custom composite types in addition to tables  even when table type is  BASE TABLE       As a consequence  we can t iterate oninformation schema tables  otherwise we risk having select count    from name of composite type and that would fail  OTOH pg class where relkind  r  should always work fine  The type of COUNT   is bigint  not int  Tables with more than 2 15 billion rows may exist  running a count    on them is a bad idea  though   A permanent type need not to be created for a function to return a resultset with several columns  RETURNS TABLE definition     is a better alternative

User · Answer

I don t remember the URL from where I collected this   But hope this should help you   CREATE TYPE table count AS  table name TEXT  num rows INTEGER     CREATE OR REPLACE FUNCTION count em all    RETURNS SETOF table count  AS   DECLARE      the count RECORD       t name RECORD       r table count ROWTYPE    BEGIN     FOR t name IN          SELECT              c relname         FROM             pg catalog pg class c LEFT JOIN pg namespace n ON n oid   c relnamespace         WHERE              c relkind     r               AND n nspname     public            ORDER BY 1          LOOP             FOR the count IN EXECUTE   SELECT COUNT    AS  count  FROM       t name relname              LOOP              END LOOP                r table name    t name relname               r num rows    the count count               RETURN NEXT r           END LOOP           RETURN   END    LANGUAGE plpgsql     Executing select count em all    should get you row count of all your tables

User · Answer

I usually don t rely on statistics  especially in PostgreSQL   SELECT table name  dsql2  select count    from    table name  as rownum FROM information schema tables WHERE table type  BASE TABLE      AND table schema  livescreen  ORDER BY 2 DESC      CREATE OR REPLACE FUNCTION dsql2 i text text    RETURNS int AS  BODY  Declare   v val int  BEGIN   execute i text into v val    return v val  END    BODY    LANGUAGE plpgsql VOLATILE   COST 100

User · Answer

I made a small variation to include all tables  also for non-public tables   CREATE TYPE table count AS  table schema TEXT table name TEXT  num rows INTEGER     CREATE OR REPLACE FUNCTION count em all    RETURNS SETOF table count  AS   DECLARE      the count RECORD       t name RECORD       r table count ROWTYPE    BEGIN     FOR t name IN          SELECT table schema table name         FROM information schema tables         where table schema     pg catalog             and table schema     information schema           ORDER BY 1 2         LOOP             FOR the count IN EXECUTE   SELECT COUNT    AS  count  FROM       t name table schema         t name table name             LOOP              END LOOP                r table schema    t name table schema              r table name    t name table name               r num rows    the count count               RETURN NEXT r           END LOOP           RETURN   END    LANGUAGE plpgsql     use select count em all    to call it   Hope you find this usefull  Paul

User · Answer

I wanted the total from all tables   a list of tables with their counts  A little like a performance chart of where most time was spent  WITH results AS      SELECT nspname AS schemaname relname reltuples     FROM pg class C     LEFT JOIN pg namespace N ON  N oid   C relnamespace      WHERE        nspname NOT IN   pg catalog    information schema   AND       relkind  r       GROUP BY schemaname  relname  reltuples    SELECT   FROM results UNION SELECT  all  AS schemaname   all  AS relname  SUM reltuples  AS  reltuples  FROM results  ORDER BY reltuples DESC   You could of course put a LIMIT clause on the results in this version too so that you get the largest n offenders as well as a total   One thing that should be noted about this is that you need to let it sit for a while after bulk imports  I tested this by just adding 5000 rows to a database across several tables using real import data  It showed 1800 records for about a minute  probably a configurable window   This is based from https   stackoverflow com a 2611745 1548557 work  so thank you and recognition to that for the query to use within the CTE

[postgresql] How do you find the row count for all your tables in Postgres

Examples related to postgresql

Examples related to count

Examples related to database-table