Database Administrators Asked on December 31, 2020
My database is in TB with different schemas. I would like to search in each schema following information:
Where table name contains: *.bak
or dropped_*
or *_drop
The tables appear as entries in pg_catalog.pg_class
that have relkind='r'
(see the documentation).
table name: pg_class.relname
schema name: pg_namespace.nspname
. Join pg_class.relnamespace
to pg_namespace.oid
to follow the association between the table and its schema.
table size: see pg_relation_size(pg_class.oid)
row count: pg_class.reltuples
gives an estimate. For an exact number, run select count(*) from the table.
created date: the creation date is not recorded by postgres.
Some of these are also available through views in information_schema
if you prefer. The structures of the views in this schema are dictated by the SQL standard, whereas the pg_catalog tables are postgres-specific.
Starting from the query provided as a comment:
select nspname as schema, relname as tablename,
reltuples as rowcounts
from pg_class c JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace where relkind='r'
and relname like '%_drop%'
order by nspname, reltuples desc
remarks:
The underscore character is a wildcard for the LIKE operator (it matches any character), so it needs to be quoted to be interpreted literally. By default, the quote character is backslash.
pg_relation_size() as a function call can be introduced directly in the select list.
Amended query:
select
nspname as schema,
relname as tablename,
reltuples as rowcounts,
pg_relation_size(c.oid) AS tablesize
from pg_class c JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace where relkind='r'
and relname like '%_drop%'
order by nspname, reltuples desc
Correct answer by Daniel Vérité on December 31, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP