postgresql - tables - sql count distinct postgres
¿Cómo encuentra el recuento de filas para todas sus tablas en Postgres? (11)
Aquí hay una solución que no requiere funciones para obtener un conteo preciso para cada tabla:
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 ejecutará la consulta SQL pasada y devolverá un XML con el resultado (el recuento de filas para esa tabla). El xpath() externo xpath() luego extraerá la información de conteo de ese xml y la convertirá en un número
La tabla derivada no es realmente necesaria, pero hace que la xpath() un poco más fácil de entender; de lo contrario, toda la consulta a query_to_xml() debería pasar a la función xpath() .
Estoy buscando una manera de encontrar el recuento de filas para todas mis tablas en Postgres. Sé que puedo hacer esto de una mesa a la vez con:
SELECT count(*) FROM table_name;
pero me gustaría ver el recuento de filas para todas las mesas y luego ordenar por eso para tener una idea de cuán grandes son todas mis mesas.
Dos pasos simples:
(Nota: No es necesario cambiar nada, solo copiar y pegar)
1. crear función
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. Ejecute esta consulta para obtener el recuento de filas para todas las tablas
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;
o
Para obtener filas cuenta en tabla
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
Hay tres formas de obtener este tipo de conteo, cada uno con sus propias compensaciones.
Si desea un conteo verdadero, debe ejecutar la instrucción SELECT como la que usó contra cada tabla. Esto se debe a que PostgreSQL mantiene la información de visibilidad de la fila en la misma fila, no en ningún otro lugar, por lo que cualquier conteo exacto solo puede ser relativo a alguna transacción. Obtiene un recuento de lo que ve la transacción en el momento en que se ejecuta. Puede automatizar esto para que se ejecute en cada tabla de la base de datos, pero probablemente no necesite ese nivel de precisión o desee esperar tanto tiempo.
El segundo enfoque señala que el recopilador de estadísticas realiza un seguimiento de aproximadamente cuántas filas están "activas" (no se eliminan ni quedan obsoletas por actualizaciones posteriores) en ningún momento. Este valor puede estar desactivado un poco en una actividad pesada, pero generalmente es una buena estimación:
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
Eso también puede mostrarle cuántas filas están muertas, lo que en sí mismo es un número interesante para monitorear.
La tercera forma es tener en cuenta que el comando ANALIZAR del sistema, que se ejecuta mediante el proceso autovacuum regularmente a partir de PostgreSQL 8.3 para actualizar las estadísticas de la tabla, también calcula una estimación de fila. Puedes agarrar eso de esta manera:
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;
Es difícil decir cuál de estas consultas es mejor usar. Normalmente tomo esa decisión en función de si hay más información útil que también quiero usar dentro de pg_class o dentro de pg_stat_user_tables. Para propósitos básicos de conteo, solo para ver qué tan grandes son las cosas en general, cualquiera debería ser lo suficientemente preciso.
Hice una pequeña variación para incluir todas las tablas, también para tablas no públicas.
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(); llamarlo
Espero que encuentres esto útil. Pablo
La respuesta intrépida y práctica para las personas que intentan evaluar qué plan Heroku necesitan y no pueden esperar a que se actualice el contador de filas lentas de heroku:
Básicamente, desea ejecutar /dt en psql , copie los resultados a su editor de texto favorito (se verá así:
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
), luego ejecuta una búsqueda de expresiones regulares y reemplaza así:
^[^|]*/|/s+([^|]*?)/s+/| table /|.*$
a:
select ''/1'', count(*) from /1 union/g
Lo que te dará algo muy parecido a esto:
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
;
(Deberás eliminar la union y agregar el punto y coma al final manualmente)
Ejecutarlo en psql y listo.
?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
[..]
Me gusta la answer Daniel Vérité. Pero cuando no puede usar una sentencia CREATE, puede usar una solución de bash o, si es un usuario de Windows, una de 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"
}
No estoy seguro si una respuesta en bash es aceptable para usted, pero 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
No recuerdo la URL de donde recogí esto. Pero espero que esto te ayude:
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;
Ejecutando select count_em_all(); Debería obtener el número de filas de todas sus tablas.
Normalmente no confío en las estadísticas, especialmente en 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;
Para obtener estimaciones, vea la respuesta de Greg Smith .
Para obtener recuentos exactos, las otras respuestas hasta ahora están plagadas de algunos problemas, algunos de ellos serios (ver más abajo). Aquí hay una versión que espero sea mejor:
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;
Toma un nombre de esquema como parámetro, o public si no se da ningún parámetro.
Para trabajar con una lista específica de esquemas o una lista proveniente de una consulta sin modificar la función, se puede llamar desde una consulta como esta:
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;
Esto produce una salida de 3 columnas con el esquema, la tabla y el conteo de filas.
Ahora aquí hay algunos problemas en las otras respuestas que esta función evita:
Los nombres de tablas y esquemas no se deben inyectar en el SQL ejecutable sin
quote_ident, ya sea conquote_idento con la funciónformat()más moderna con su cadena de formato%IDe lo contrario, alguna persona maliciosa puede nombrar su tabla nombre de tablatablename;DROP TABLE other_tableque es perfectamente válido como nombre de tabla.Incluso sin la inyección de SQL y los problemas de caracteres divertidos, el nombre de la tabla puede existir en variantes diferentes según el caso. Si una tabla se llama
ABCDy otraabcd, elSELECT count(*) FROM...debe usar un nombre entre comillas, de lo contrario saltearáABCDy contaráabcddos veces. El%Ide formato lo hace automáticamente.information_schema.tablesenumera tipos compuestos personalizados además de tablas, incluso cuando table_type es''BASE TABLE''(!). Como consecuencia, no podemos iterar eninformation_schema.tables, de lo contrario, nos arriesgamos a tener unselect count(*) from name_of_composite_typey eso fallaría. OTOHpg_class where relkind=''r''siempre debería funcionar bien.El tipo de COUNT () es
bigint, noint. Sin embargo, es posible que existan tablas con más de 2,150 millones de filas (sin embargo, ejecutar una cuenta (*) en ellas).No es necesario crear un tipo permanente para que una función devuelva un conjunto de resultados con varias columnas.
RETURNS TABLE(definition...)es una mejor alternativa.
Si no le importa la posibilidad de datos obsoletos, puede acceder a las mismas estadísticas que utiliza el optimizador de consultas .
Algo como:
SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;