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).
SOCIAL SHARE CARD GENERATOR