Як в Posgresql 16 вивести список всіх не системних таблиць та кількість рядків в них, просортувати рядки від більшого до меншого

Щоб вивести список всіх не системних таблиць та кількість рядків в них і просортувати рядки від більшого до меншого в PostgreSQL 16, ви можете використовувати наступний SQL-запит:

SELECT
    table_name,
    row_count
FROM (
    SELECT
        table_name,
        row_count
    FROM (
        SELECT
            table_name,
            row_count,
            ROW_NUMBER() OVER (ORDER BY row_count DESC) AS rn
        FROM (
            SELECT
                table_name,
                pg_total_relation_size(`public.` || table_name) / 8192 AS row_count
            FROM information_schema.tables
            WHERE table_schema = `public` AND table_type = `BASE TABLE`
                AND table_name NOT LIKE `pg_%`
                AND table_name NOT IN (`information_schema`, `pg_stat_statements`, `pg_buffercache`)
        ) AS subquery
    ) AS subquery_with_row_number
    WHERE rn <= 10 -- Показати топ 10 таблиць за кількістю рядків
) AS top_tables
ORDER BY row_count DESC;

Цей запит робить наступне:

Вибирає всі таблиці в схемі "public", крім системних, та обчислює кількість рядків в кожній таблиці, використовуючи функцію pg_total_relation_size.
Сортує результати в порядку спадання кількості рядків.
Вибирає топ 10 таблиць за кількістю рядків.

Запит виведе список таблиць та кількість рядків в них у порядку від більшого до меншого. Вам може знадобитися змінити значення rn в WHERE rn <= 10, якщо ви хочете вивести іншу кількість таблиць у результатах.

Вивести список всіх таблиць та кількість рядків в них (Протестовано на Postgresql 14)

Для отримання кількості записів в кожній таблиці в PostgreSQL 14, ви можете використовувати наступний SQL-запит:

SELECT schemaname, tablename, (xpath(`/row/cnt/text()`, xml_count))[1]::text::int AS row_count
FROM (
  SELECT schemaname, tablename,
         query_to_xml(format(`SELECT count(*) as cnt FROM %I.%I`, schemaname, tablename), false, true, ``) AS xml_count
  FROM pg_tables
) AS counts
ORDER BY schemaname, tablename;

Цей запит використовує функцію pg_tables для отримання списку таблиць у базі даних і генерує SQL-запити для підрахунку кількості записів в кожній таблиці. Результат буде містити назву схеми, назву таблиці та кількість записів в кожній таблиці.

Зверніть увагу, що цей запит працює у схемі за замовчуванням. Якщо ви працюєте в інших схемах, замініть 'public' на відповідну назву схеми у запиті.

Запускайте цей SQL-запит у вашій PostgreSQL 14 базі даних, і він повинен повернути кількість записів в кожній таблиці.

Автор: Рудюк С.А. 2023. K2 Cloud ERP.