Re: Toast table infi

Hi

Please try out the following query.

This will display all the tables with associated Toast tables in the
cluster.

SELECT
n.nspname AS schema_name,
c.relname AS table_name,
t.relname AS toast_table_name
FROM
pg_class c
JOIN
pg_namespace n ON c.relnamespace = n.oid
JOIN
pg_attribute a ON c.oid = a.attrelid
LEFT JOIN
pg_class t ON a.attrelid = t.reltoastrelid
WHERE
c.relkind = 'r' -- Regular tables
AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- Exclude
system schemas
AND c.reltoastrelid != 0 -- Only tables with toast tables
ORDER BY
n.nspname, c.relname;

To check if a table has a toast table .

select t1.oid, t1.relname, t1.relkind, t2.relkind, t2.relpages,
t2.reltuplesfrom pg_class t1inner join pg_class t2on t1.reltoastrelid
= t2.oidwhere t1.relkind = 'r'
and t2.relkind = 't';

Regards

Kashif Zeeshan

Bitnine Global

On Mon, May 27, 2024 at 10:50 AM Sathish Reddy <
sathishreddy(dot)postgresql(at)gmail(dot)com> wrote:

> Hi
> I am trying to get toast tables information from cluster level all
> databases tables with child tables.but not working.please help me on
> queries to sort out these.
>
> Thanks
> Sathish Reddy
>