pg_class in postgreSQL

The pg_class system catalog in PostgreSQL contains metadata about the tables, indexes, sequences, views, and other relations in the database. It is a central part of PostgreSQL's internal architecture, and you can query pg_class to obtain detailed information about the structure of your database.

1. List All Relations (Tables, Views, Sequences)

This query will give you a list of all relations (tables, views, sequences, etc.) in the current database:

SELECT 
    relname AS relation_name, 
    relkind AS relation_type,
    CASE 
        WHEN relkind = 'r' THEN 'Table'
        WHEN relkind = 'v' THEN 'View'
        WHEN relkind = 'i' THEN 'Index'
        WHEN relkind = 'S' THEN 'Sequence'
        WHEN relkind = 't' THEN 'TOAST table'
        WHEN relkind = 'c' THEN 'Composite type'
        ELSE 'Other'
    END AS relation_type_description
FROM 
    pg_class
WHERE 
    relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') -- limit to 'public' schema
ORDER BY 
    relname;

2. Get Table Size

If you want to find the size of a specific table (including its TOAST table, indexes, etc.), you can use pg_class along with the pg_total_relation_size() function:

SELECT 
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM 
    pg_class c
WHERE 
    c.relkind = 'r'  -- 'r' stands for regular tables
ORDER BY 
    pg_total_relation_size(c.oid) DESC;

3. Find Tables with Most Indexes

To find which tables have the most indexes, you can join pg_class with pg_index and group by the table OID:

SELECT 
    t.relname AS table_name,
    COUNT(i.indexrelid) AS index_count
FROM 
    pg_class t
JOIN 
    pg_index i ON t.oid = i.indrelid
WHERE 
    t.relkind = 'r' -- only tables
GROUP BY 
    t.relname
ORDER BY 
    index_count DESC;

4. List All Indexes and Their Associated Tables

If you're interested in finding all indexes in the database and the tables they are associated with, use the following query:

SELECT 
    idx.relname AS index_name,
    tbl.relname AS table_name
FROM 
    pg_class tbl
JOIN 
    pg_index ix ON tbl.oid = ix.indrelid
JOIN 
    pg_class idx ON ix.indexrelid = idx.oid
WHERE 
    tbl.relkind = 'r'  -- only tables
ORDER BY 
    tbl.relname, idx.relname;

5. Get Column Count for Each Table

To get the number of columns for each table, you can join pg_class with pg_attribute:

SELECT 
    c.relname AS table_name,
    COUNT(a.attname) AS column_count
FROM 
    pg_class c
JOIN 
    pg_attribute a ON c.oid = a.attrelid
WHERE 
    c.relkind = 'r' -- only tables
    AND a.attnum > 0  -- exclude system columns
GROUP BY 
    c.relname
ORDER BY 
    column_count DESC;

6. List All Constraints on a Table

To list all constraints on a table (e.g., primary keys, foreign keys, unique constraints), you can query the pg_constraint catalog:

SELECT 
    c.conname AS constraint_name,
    c.contype AS constraint_type,
    t.relname AS table_name
FROM 
    pg_constraint c
JOIN 
    pg_class t ON c.conrelid = t.oid
WHERE 
    t.relname = 'your_table_name';  -- Replace with the table you're interested in

7. Find Tables with Foreign Keys

To find tables with foreign keys, you can query pg_constraint and pg_class as follows:

SELECT 
    t.relname AS table_name,
    c.conname AS foreign_key_name
FROM 
    pg_constraint c
JOIN 
    pg_class t ON c.conrelid = t.oid
WHERE 
    c.contype = 'f';  -- 'f' for foreign keys

• contype = 'f': Filters for foreign key constraints.

8. Find Tables with No Indexes

You can also identify tables that do not have any indexes:

SELECT 
    t.relname AS table_name
FROM 
    pg_class t
LEFT JOIN 
    pg_index i ON t.oid = i.indrelid
WHERE 
    t.relkind = 'r'  -- only tables
    AND i.indexrelid IS NULL
ORDER BY 
    t.relname;

9. Find Large Tables (by Size)

To identify the largest tables in terms of total size, you can use pg_class together with pg_total_relation_size():

SELECT 
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM 
    pg_class c
WHERE 
    c.relkind = 'r' -- only tables
ORDER BY 
    pg_total_relation_size(c.oid) DESC
LIMIT 10;

• This query retrieves the 10 largest tables based on their total size.

10. Show Tables with Their OIDs

To get the OID (Object Identifier) of tables, you can query pg_class directly:

SELECT 
    oid,
    relname AS table_name
FROM 
    pg_class
WHERE 
    relkind = 'r'; -- only tables

• oid: A unique identifier for each object in PostgreSQL (including tables).

---