Postgres에서 모든 테이블의 행 수를 어떻게 찾습니까?
저는 Postgres에 있는 제 모든 테이블의 행 수를 찾을 방법을 찾고 있습니다.한 번에 하나의 테이블을 사용할 수 있습니다.
SELECT count(*) FROM table_name;
하지만 저는 모든 테이블의 행 수를 보고 그 수만큼 주문하여 제 모든 테이블이 얼마나 큰지 알고 싶습니다.
세 가지 방법으로 계산할 수 있습니다. 각각의 방법은 그들만의 트레이드오프가 있습니다.
실제 카운트를 원한다면 각 테이블에 대해 사용한 것처럼 SELECT 문을 실행해야 합니다.이것은 Postgre가SQL은 행 가시성 정보를 다른 곳이 아닌 행 자체에 보관하므로 정확한 카운트는 일부 트랜잭션에만 관련될 수 있습니다.해당 트랜잭션이 실행되는 시점에 표시되는 내용을 계산합니다.데이터베이스의 모든 테이블에 대해 이 작업을 자동화하여 실행할 수 있지만, 이러한 수준의 정확성은 필요하지 않거나 오래 기다릴 필요가 없습니다.
WITH tbl AS
(SELECT table_schema,
TABLE_NAME
FROM information_schema.tables
WHERE TABLE_NAME not like 'pg_%'
AND table_schema in ('public'))
SELECT table_schema,
TABLE_NAME,
(xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;
두 번째 접근 방식은 통계 수집기가 언제든지 대략 얼마나 많은 행이 "활성"(이후 업데이트에서 삭제되거나 사용되지 않음)인지 추적한다는 점에 주목합니다.이 값은 작업량이 많은 경우 약간 떨어질 수 있지만 일반적으로 다음과 같은 추정치가 좋습니다.
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
또한 비활성 행 수를 표시할 수 있으며, 이는 모니터링하기에 흥미로운 숫자입니다.
세 번째 방법은 시스템 분석 명령어가 Postgre 기준으로 자동 진공 프로세스에 의해 정기적으로 실행된다는 점에 유의하는 것입니다.테이블 통계를 업데이트하는 SQL 8.3에서는 행 추정치도 계산합니다.다음과 같이 잡을 수 있습니다.
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;
이러한 쿼리 중 어느 것이 더 사용하기 좋은지는 말하기 어렵습니다.일반적으로 저는 pg_class 내부 또는 pg_stat_user_tables 내부에 더 유용한 정보가 있는지 여부에 따라 결정합니다.기본적인 계산 목적을 위해서는 일반적으로 사물이 얼마나 큰지 확인하는 것이 어느 것이든 충분히 정확해야 합니다.
각 테이블에 대한 정확한 카운트를 얻기 위해 함수가 필요하지 않은 솔루션은 다음과 같습니다.
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전달된 SQL 쿼리를 실행하고 결과(해당 테이블의 행 수)가 포함된 XML을 반환합니다. 바깥쪽.xpath() 다음 "xml"로 변환합니다.
파생된 표는 실제로 필요하지 않지만, 다음을 만듭니다.xpath()더 하기 쉬운 - 전체적으로 - 그지않으면다전체니 - 렇금더습쉽기해이.query_to_xml()로 전달되어야 할 것입니다.xpath()기능.
견적을 얻으려면 Greg Smith의 답변을 참조하십시오.
정확한 카운트를 얻기 위해 지금까지의 다른 답변은 몇 가지 문제로 인해 어려움을 겪고 있으며, 일부는 심각합니다(아래 참조).다음은 더 나은 버전입니다.
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;
변수로 합니다.public매개 변수가 지정되지 않은 경우
함수를 수정하지 않고 쿼리에서 가져온 특정 스키마 목록 또는 목록으로 작업하려면 다음과 같은 쿼리 내에서 호출할 수 있습니다.
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;
이렇게 하면 스키마, 테이블 및 행 수와 함께 3열 출력이 생성됩니다.
다음은 이 함수가 회피하는 다른 답변의 몇 가지 문제입니다.
따옴표를 한 SQL에 .
quote_ident좀 더 ▁the.format()능께것▁its▁with.%I형식 문자열그렇지 않으면 일부 악의적인 사람이 테이블 이름을 지정할 수 있습니다.tablename;DROP TABLE other_table테이블 이름으로 완벽하게 유효합니다.SQL 주입 및 재미있는 문자 문제가 없더라도 테이블 이름은 대소문자마다 다른 변형으로 존재할 수 있습니다.테이블 이름이 지정된 경우
ABCD또 의 또나하.abcd,SELECT count(*) FROM...로 묶은 . 을 건너뜁니다. 그렇지 않으면 건너뜁니다.ABCD어세보 다어보.abcd번. 두그. 번그.%I자동으로 이 작업을 수행합니다.information_schema.tables이 table_type인 에도 사용자 에 나열되어 .'BASE TABLE'(!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!), (!information_schema.tables그렇지 않으면 우리는 가질 위험이 있습니다.select count(*) from name_of_composite_type그리고 그것은 실패할 것입니다.OTOHpg_class where relkind='r'항상 잘 작동해야 합니다.은 COUNT()입니다.
bigint,것은 아니다.int21억 5천만 개 이상의 행이 있는 테이블이 존재할 수 있습니다(그러나 행에 카운트(*)를 실행하는 것은 좋지 않습니다).여러 열의 결과 집합을 반환하는 함수에 대해 영구 형식을 만들 필요는 없습니다.
RETURNS TABLE(definition...)더 나은 대안입니다.
헤로쿠의 느린 행 카운터가 새로 고쳐지기를 기다릴 수 없는 헤로쿠 계획을 평가하려는 사람들을 위한 진부하고 실용적인 답변:
기본적으로 당신은 실행하기를 원합니다.\dtpsql결과를 즐겨찾는 텍스트 편집기에 복사합니다(다음과 같이 표시됩니다).
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
), 그런 다음 정규식 검색을 실행하고 다음과 같이 바꿉니다.
^[^|]*\|\s+([^|]*?)\s+\| table \|.*$
대상:
select '\1', count(*) from \1 union/g
이것과 매우 유사한 결과를 얻을 수 있습니다.
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
;
(마지막항제합니다야해거을목▁the합마)을 제거해야 합니다union마지막에 세미콜론을 수동으로 추가합니다.
에서 psql그리고 당신은 끝났습니다.
?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
[..]
잠재적으로 오래된 데이터가 문제가 되지 않는 경우 쿼리 최적화 프로그램에서 사용하는 것과 동일한 통계에 액세스할 수 있습니다.
다음과 같은 것:
SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;
두 간한두단계:
아무것도 - 복사 만 함) (파일: 변할필요없음됨하 - 만면기붙넣여복사참경)
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;
이 쿼리를 실행하여 모든 테이블에 대한 행 수를 가져옵니다.
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;
또는
행 개수를 표 단위로 가져오는 방법
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;
bash의 답변이 당신에게 받아들여질 수 있는지 확실하지 않지만, 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
보다 읽기 쉽게 하기 위해 Greg Smith의 답변에 있는 내 의견에서 발췌한 것입니다.
with tbl as (
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_name not like 'pg_%' AND table_schema IN ('public')
)
SELECT
table_schema,
table_name,
(xpath('/row/c/text()',
query_to_xml(format('select count(*) AS c from %I.%I', table_schema, table_name),
false,
true,
'')))[1]::text::int AS rows_n
FROM tbl ORDER BY 3 DESC;
@a_horse_with_no_name 덕분입니다.
저는 보통 통계에 의존하지 않습니다, 특히 포스트그레에서.SQL.
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;
이것은 나에게 효과가 있었습니다.
스키마 이름,relname,n_live_tup을 pg_stat_user_tables에서 n_live_tup DESC로 선택합니다.
이것을 어디서 모았는지 URL이 기억나지 않습니다.하지만 이것이 당신에게 도움이 되기를 바랍니다.
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;
을 실행하는 중select count_em_all();당신의 모든 테이블의 행 수를 가져올 것입니다.
공용이 아닌 테이블에 대해서도 모든 테이블을 포함하도록 약간 변경했습니다.
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;
사용하다select count_em_all();말하자면
이것이 유용하기를 바랍니다.폴.
이 쿼리를 사용하여 해당 개수의 모든 테이블 이름을 생성할 수 있습니다.
select ' select '''|| tablename ||''', count(*) from ' || tablename ||'
union' from pg_tables where schemaname='public';
위 질문의 결과는 다음과 같습니다.
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
마지막 유니언을 제거하고 마지막에 세미콜론을 추가해야 합니다!!
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 **;**
뛰어!!!
여기 훨씬 더 간단한 방법이 있습니다.
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
출력은 다음과 같아야 합니다.
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
다있니습을 수.psql -U "${PGUSER}" 데 입니다.
로 에여.head -n-2구문은 macOS에서 작동하지 않을 수 있습니다. 아마도 거기서 다른 구현을 사용할 수 있습니다.
psql(Postgre)에서 테스트됨SQL) 11.2 중심 아래OS 7
테이블별로 정렬하려면, 그냥 그것으로 포장하세요.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
출력;
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
다니엘 베리테의 대답이 마음에 듭니다.그러나 CREATE 문을 사용할 수 없는 경우 bash 솔루션을 사용하거나 Windows 사용자인 경우 파워셸 솔루션을 사용할 수 있습니다.
# 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"
}
저는 모든 표의 합계 + 그들의 수가 포함된 표 목록을 원했습니다.대부분의 시간을 사용한 성능 차트와 유사합니다.
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
물론 당신은 그것을 넣을 수 있습니다.LIMIT이 버전의 결과에 대한 조항은 당신이 가장 큰 것을 얻을 수 있도록 합니다.n범죄자들과 전체적인 사람들.
이에 대해 한 가지 주의해야 할 점은 대량 수입 후 잠시 방치해 두어야 한다는 것입니다.실제 가져오기 데이터를 사용하여 데이터베이스에 5,000개의 행을 여러 테이블에 추가하여 테스트했습니다.약 1분 동안 1800개의 레코드를 보여주었습니다(아마도 구성 가능한 창).
이는 https://stackoverflow.com/a/2611745/1548557 작업을 기반으로 합니다. CTE 내에서 사용할 쿼리에 대한 감사 및 인식입니다.
당신이 .psql셸을 사용하면 외부 텍스트 편집기에서 수동으로 편집하지 않고 syed의 응답 및 Aur의 응답에 설명된 구문을 실행할 수 있습니다.
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
참고, 구분하는 데 모두 사용됩니다.union all명령문 사이에서 분리된 데이터 행을 버퍼로 전달할 단일 단위로 스ush합니다.
\gexec현재 쿼리 버퍼를 서버로 보낸 다음 쿼리 출력의 각 행(있는 경우)의 각 열을 실행할 SQL 문으로 처리합니다.
아래 쿼리는 각 테이블에 대한 행 수와 크기를 제공합니다.
table_dll, table_name, pg_dll_size('"||table_dll||'를 선택합니다.""|table_name||'")/1024/1024 size_MB, (xpath('/row/c/text(), query_to_xml(format('select count(*) ASc from %I).%I', table_schema, table_name), false, true, ''), [1]::text::information_schema.tables에서 크기순으로 정렬_MB desc;
각 테이블의 크기와 행 수를 출력하는 쿼리는 다음과 같습니다.
SELECT
pg_class.relname AS table_name,
pg_size_pretty(pg_total_relation_size(pg_class.oid)) AS size,
pg_total_relation_size(pg_class.oid) / (current_setting('block_size')::integer / 1024) AS num_blocks,
pg_stat_user_tables.n_live_tup AS num_rows
FROM
pg_stat_user_tables
JOIN
pg_class ON pg_stat_user_tables.relid = pg_class.oid
WHERE
pg_class.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
ORDER BY
pg_total_relation_size(pg_class.oid) DESC;
언급URL : https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres
'programing' 카테고리의 다른 글
| Postgre에서 외부 키가 있는 행 삭제SQL (0) | 2023.05.07 |
|---|---|
| 애저 데브옵스 파이프라인에서 사용자 환경 변수를 설정하고 읽는 방법은 무엇입니까? (0) | 2023.05.07 |
| SQL Server의 IsNull() 함수에 해당하는 C# (0) | 2023.05.07 |
| 깃 저장소에서 이전 기록을 제거하려면 어떻게 해야 합니까? (0) | 2023.05.07 |
| 각각의 새로운 캐릭터에 WPF TextBox 바인딩 불을 붙입니까? (0) | 2023.05.07 |