Jak znaleźć liczbę wierszy dla wszystkich tabel w Postgres

Oto rozwiązanie, które nie wymaga funkcji, aby uzyskać dokładną liczbę dla każdej tabeli:

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' --<< change here for the schema you want
) t

query_to_xml uruchomi przekazane zapytanie SQL i zwróci XML z wynikiem (liczba wierszy dla tej tabeli). Zewnętrzne xpath() wyodrębni informacje o liczbie z tego xml i skonwertuje je na liczbę

Pochodna tabela nie jest tak naprawdę potrzebna, ale sprawia, że xpath() jest nieco łatwiejsza do zrozumienia - w przeciwnym razie cała query_to_xml() musiałaby zostać przekazana do funkcji xpath().

Aby uzyskać szacunki, zobacz odpowiedź Grega Smitha .

Aby uzyskać dokładne obliczenia, inne odpowiedzi do tej pory są nękane pewnymi problemami, niektóre z nich poważne(patrz poniżej). Oto wersja, która jest mam nadzieję lepsza:

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;

Przyjmuje nazwę schematu jako parametr, lub public jeśli nie podano żadnego parametru.

Aby pracować z określoną listą schematów lub listą pochodzącą z zapytania bez modyfikowania funkcji, można ją wywołać z poziomu zapytania jak to:

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;

Tworzy to wyjście 3-kolumnowe ze schematem, tabelą i liczbą wierszy.

Oto kilka problemów w innych odpowiedziach, których ta funkcja unika:

• Nazwy tabel i schematów nie powinny być wstrzykiwane do wykonywalnego SQL bez cytowania, za pomocą quote_ident lub bardziej nowoczesnej funkcji format() z łańcuchem formatu %I. W przeciwnym razie jakaś złośliwa osoba może nazwać swoją tabelę tablename;DROP TABLE other_table, która jest całkowicie poprawna jako tabela nazwisko.

• Nawet bez problemów z SQL injection i funny characters, nazwa tabeli może występować w wariantach różniących się wielkością liter. Jeśli tabela ma nazwę ABCD i inną abcd, to SELECT count(*) FROM... musi użyć cytowanej nazwy, w przeciwnym razie pominie ABCD i policzy abcd dwa razy. Format %I robi to automatycznie.

• information_schema.tables lista niestandardowych typów złożonych oprócz tabel, nawet jeśli table_type to 'BASE TABLE' (!). W związku z tym nie możemy iterować na information_schema.tables, w przeciwnym razie ryzykujemy posiadanie select count(*) from name_of_composite_type i to by się nie udało. OTOH pg_class where relkind='r' powinien zawsze działać dobrze.

• Typ COUNT() to bigint, a nie int. Mogą istnieć tabele zawierające ponad 2,15 miliarda wierszy (uruchamianie na nich liczenia(*) jest jednak złym pomysłem).

• Aby funkcja zwróciła zestaw wyników z kilkoma kolumnami, nie musi być tworzony Stały typ. To lepsza alternatywa.

Jeśli nie masz nic przeciwko potencjalnie przestarzałym danym, Możesz uzyskać dostęp do tych samych statystyk używanych przez optymalizator zapytań.

Coś w stylu:

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

Hakerska, praktyczna odpowiedź dla osób próbujących ocenić, jakiego planu Heroku potrzebują i nie mogą się doczekać, aż licznik rzędów heroku odświeży:

W zasadzie chcesz uruchomić \dt w psql, skopiuj wyniki do swojego ulubionego edytora tekstu (będzie wyglądał tak:

 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

), następnie uruchom wyszukiwanie regex i zamień tak:

^[^|]*\|\s+([^|]*?)\s+\| table \|.*$

Do:

select '\1', count(*) from \1 union/g

Co da ci coś bardzo podobnego do tego:

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
;

(musisz usunąć ostatnią union i ręcznie dodać średnik na końcu)

Uruchom go psql i gotowe.

            ?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
[..]

Nie wiem, czy odpowiedźw bash jest dla Ciebie do przyjęcia, ale 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

Proste dwa kroki:
(uwaga : nie trzeba niczego zmieniać - wystarczy skopiować wklej)
1. Utwórz funkcję

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. Uruchom to zapytanie, aby uzyskać liczbę wierszy dla wszystkich tabel

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;

Lub

aby uzyskać liczbę wierszy 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;

Zazwyczaj nie polegam na statystykach, szczególnie w 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;

Nie pamiętam adresu URL, z którego to zebrałem. Ale mam nadzieję, że to ci pomoże:

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; 

Wykonanie select count_em_all(); powinno dać ci liczbę wierszy wszystkich tabel.

Zrobiłem małą odmianę, aby uwzględnić wszystkie tabele, również dla tabel niepublicznych.

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; 

Użyj select count_em_all();, aby to nazwać.

Mam nadzieję, że uznasz to za przydatne. Paweł

This worked for me

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

Podoba mi się odpowiedź Daniela Vérité. Ale jeśli nie możesz użyć instrukcji CREATE, możesz użyć rozwiązania bash lub, jeśli jesteś użytkownikiem systemu windows, powershell:

# You don't need this if you have pgpass.conf
$env:PGPASSWORD = "userpass"

# Get table list
$tables = & '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) {
    & 'C:\path_to_postresql\bin\psql.exe' -U root -w -d dbname -At -c "select '$table', count(*) from $table"
}

Tutaj jest o wiele prostszy sposób.

tables="$(echo '\dt' | psql -U "${PGUSER}" | tail -n +4 | head -n-2 | tr -d ' ' | cut -d '|' -f2)"
for table in $tables; do
printf "%s: %s\n" "$table" "$(echo "SELECT COUNT(*) FROM $table;" | psql -U "${PGUSER}" | tail -n +3 | head -n-2 | tr -d ' ')"
done

Wyjście powinno wyglądać tak

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

Możesz zaktualizować psql -U "${PGUSER}" część w razie potrzeby, aby uzyskać dostęp do bazy danych

Zauważ, że składnia head -n-2 może nie działać w macOS, prawdopodobnie możesz użyć innej implementacji tam

Testowane na psql (PostgreSQL) 11.2 pod CentOS 7

Jeśli chcesz posortować go według tabeli, po prostu zawiń go za pomocą sort

for table in $tables; do
printf "%s: %s\n" "$table" "$(echo "SELECT COUNT(*) FROM $table;" | psql -U "${PGUSER}" | tail -n +3 | head -n-2 | tr -d ' ')"
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

Chciałem suma ze wszystkich tabel + lista tabel z ich licznikami. Trochę jak wykres wydajności, gdzie najwięcej czasu spędzono

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

Można oczywiście umieścić LIMIT klauzulę na wyniki w tej wersji również tak, aby uzyskać największe n przestępców, jak również w sumie.

Jedną z rzeczy, które należy zwrócić uwagę na to, że trzeba pozostawić go na chwilę po masowym imporcie. Przetestowałem to po prostu dodając 5000 wierszy do bazy danych w kilku tabelach za pomocą rzeczywiste dane importowe. Wyświetlał 1800 rekordów przez około minutę (prawdopodobnie konfigurowalne Okno)

To jest oparte na https://stackoverflow.com/a/2611745/1548557 praca, więc dziękuję i uznanie dla tego za zapytanie do wykorzystania w CTE

Możesz użyć tego zapytania do wygenerowania wszystkich nazw tablenów z ich licznikami

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

Wynikiem powyższego zapytania będzie

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

Musisz usunąć ostatnią Unię i dodać średnik na końcu !!

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  **;**

Jeśli jesteś w powłoce psql, Użyj \gexec pozwala na wykonanie składni opisanej w odpowiedzi Syeda i odpowiedzi Aura bez ręcznych edycji w zewnętrznym edytorze tekstu.

with x (y) as (
    select
        'select count(*), '''||
        tablename||
        ''' as "tablename" from '||
        tablename||' '
    from pg_tables
    where schemaname='public'
)
select
    string_agg(y,' union all '||chr(10)) || ' order by tablename'
from x \gexec

Uwaga, string_agg() jest używany zarówno do rozdzielania union all pomiędzy instrukcjami, jak i do tłumienia rozdzielonych zbiorów danych w jedną jednostkę przekazywaną do bufora.

\gexec

Wysyła bieżący bufor zapytań do serwera, następnie traktuje każdą kolumnę każdego wiersza z wyjścia zapytania (jeśli istnieje) jako polecenie SQL, które ma zostać wykonane.