A l'honneur

Top 10

PostgreSQL : Requêtes pour avoir le nombre d'enregistrements et la taille des tables en une seule requêtes

Détails
Clics : 607
Requête pour toutes les tables de la base :
SELECT
  string_agg(
    format(
      $$
      SELECT
        %L AS schema_name,
        %L AS table_name,
        COUNT(*)::bigint AS exact_row_count,
        pg_size_pretty(pg_total_relation_size(%L::regclass)) AS total_size
      FROM %I.%I
      $$,
      n.nspname,
      c.relname,
      (quote_ident(n.nspname) || '.' || quote_ident(c.relname)),
      n.nspname,
      c.relname
    ),
    ' UNION ALL '
  ) AS qry
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND has_table_privilege(format('%I.%I', n.nspname, c.relname), 'SELECT');
\gexec

 

Requêtes pour les tables ayant un certain nom :
SELECT
  string_agg(
    format(
      $$
      SELECT
        %L AS schema_name,
        %L AS table_name,
        COUNT(*)::bigint AS exact_row_count,
        pg_size_pretty(pg_total_relation_size(%L::regclass)) AS total_size
      FROM %I.%I
      $$,
      n.nspname,
      c.relname,
      (quote_ident(n.nspname) || '.' || quote_ident(c.relname)),
      n.nspname,
      c.relname
    ),
    ' UNION ALL '
  ) AS qry
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relname LIKE '<nom_table>%';
\gexec

 

Requête pour une seule table :
1ière méthode :
SELECT
  current_schema() AS schema_name,
  '<nom_table>' AS table_name,
  COUNT(*)::bigint AS exact_row_count,
  pg_size_pretty(pg_total_relation_size('<nom_schema>.<nom_table>'::regclass)) AS total_size
FROM <nom_schema>.<nom_table>;
2ième méthode :
SELECT
  string_agg(
    format(
      $$
      SELECT
        %L AS schema_name,
        %L AS table_name,
        COUNT(*)::bigint AS exact_row_count,
        pg_size_pretty(pg_total_relation_size(%L::regclass)) AS total_size
      FROM %I.%I
      $$,
      n.nspname,
      c.relname,
      (quote_ident(n.nspname) || '.' || quote_ident(c.relname)),
      n.nspname,
      c.relname
    ),
    ' UNION ALL '
  ) AS qry
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relname = '<nom_table>'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
  AND has_table_privilege(format('%I.%I', n.nspname, c.relname), 'SELECT');
\gexec
3ieme méthode moins précise :
SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  c.reltuples::bigint AS row_estimate,
  pg_size_pretty(pg_total_relation_size(format('%I.%I', n.nspname, c.relname)::regclass)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relname = '<nom_table>'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY total_size DESC;

Menu