List Tables in a Schema Postgres
In PostgreSQL, one of the most fundamental operations is to list tables in a schema so that developers, administrators, and auditors can quickly understand the current layout of your data domain. A reliable way to retrieve this metadata is through the built‑in catalog systems, which store detailed information about every object—tables, views, sequences, and functions—inside each database or schema. Mastering how to extract and interpret these records will save you hours of manual exploration and help maintain consistency across environments. This guide walks you through the essential techniques, explains the underlying concepts, and highlights best practices for efficiently querying PostgreSQL’s internal structures That's the part that actually makes a difference..
Introduction
PostgreSQL’s schemas act as logical containers that group related objects such as tables, procedures, and roles. Day to day, when you need to know which tables exist inside a particular schema—whether it’s the default public schema or a custom one like my_app—you have several options ranging from simple CLI commands to powerful SQL queries against the system catalogs. The following article provides a comprehensive, beginner‑friendly overview of the process, delving into both the practical “how‑to” steps and the theoretical background behind metadata management. By the end, you’ll be able to generate accurate lists of tables, their columns, and constraints, making your database audits smoother and your migration scripts more reliable.
What Is a Database Schema?
A database schema defines the organization of data within a relational database. It determines:
- Which tables belong together.
- Their relationships through foreign keys.
- Naming conventions and permissions.
- Indexing strategies and storage parameters.
In PostgreSQL, every table lives under its own schema, and many databases even contain multiple schemas to separate concerns (e.g., analytics, staging). Understanding the concept of a schema helps you decide whether you’re working in the global public area or in a dedicated application schema. When you ask to list tables in a schema, you’re essentially asking PostgreSQL to return the collection of tables that reside beneath that specific container.
How to List Tables – Step‑by‑Step Guide
Below are three proven methods to retrieve a list of tables within a chosen schema. Choose the one that matches your workflow (interactive shell, scripting, or command line) Still holds up..
1. Connect to the Target Database
First, open your PostgreSQL client (psql, DBeaver, pgAdmin, or any language library such as psycopg2). Replace the placeholders with your actual connection details:
psql -U -d
If you work remotely, you can supply credentials via environment variables (PGPASSWORD) or use SSH tunneling. Once connected, you land in the server’s default schema, typically named public. To switch to another schema, simply specify its name after \c:
\c my_app;
2. Query information_schema.tables
The most straightforward method leverages PostgreSQL’s public catalog view information_schema.This leads to tables. This view returns rows for every table, view, sequence, and function defined in the database, along with many useful attributes such as column names, data types, primary keys, and foreign keys.
Basic query (all databases):
SELECT table_name,
column_name,
data_type,
character_constraint,
collation,
is_nullable,
row_version
FROM information_schema.columns
WHERE table_space_name = 'public'
ORDER BY ordinal_position;
To restrict to a single schema, add AND table_schema = 'my_app'. For example:
SELECT tc.table_name,
ct.column_name,
ct.data_type,
ct.nullable
FROM information_schema.table_relationships tc
JOIN information_schema.columns ct ON ct.table_name = tc.table_name
WHERE tc.schemaname = 'my_app'
ORDER BY tc.sequence_in_table, ct.ordinal_position;
This returns only the columns belonging to tables in my_app. If you prefer a concise list of just table names, you can simplify:
SELECT
```sql
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'my_app'
ORDER BY table_name;
This returns only the table names in the my_app schema. If you prefer a concise list of just table names, you can simplify:
2. Use the \dt Command in psql
If you’re working interactively in the psql shell, the fastest way to list tables is with the meta-command \dt. g.This command filters by schema and displays table names along with their type (e., regular table, partitioned table).
Syntax:
\dt [schema_name]*
Examples:
-
List all tables in the current schema:
\dt -
List tables in a specific schema:
\dt my_app -
List tables across all schemas:
\dt *
The output includes columns for Schema, Name, Type, and Owner, giving you a quick overview without writing a query. For scripting or automation, you can combine \dt with \pset to format the output as CSV, HTML, or other formats.
3. Query the System Catalogs
For advanced use cases—such as when you need to filter by table properties, owner, or access privileges—PostgreSQL’s system catalogs offer direct access to metadata. The relevant catalogs are pg_class (which stores table names) and pg_namespace (which stores schema names).
Example query:
SELECT n.nspname AS schema_name,
c.relname AS table_name,
c.relkind AS table_type,
u.usename AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_user u ON u.usesysid = c.relowner
WHERE n.nspname = 'my_app'
AND c.relkind = 'r' -- 'r' for regular tables
ORDER BY n.nspname, c.relname;
This query returns the schema, table name, table type (e.On top of that, g. That's why , r for regular, p for partitioned), and owner for each table in my_app. You can adjust the relkind filter to include views (v), sequences (S), or other objects.
Choosing the Right Method
- Use
information_schema.tablesfor standard SQL compatibility and when you need detailed column-level metadata. - Use
\dtin psql for quick, interactive exploration. - Query system catalogs when you require fine-grained control or need to integrate with tools that directly access PostgreSQL internals.
Each method serves a distinct purpose, and understanding them ensures you can efficiently work through any PostgreSQL database. Whether you’re debugging, documenting, or automating, these techniques provide the foundation for working with tables across schemas.
4. Expanding the Scope: Beyond Plain Tables
While listing only regular tables is useful, many development and maintenance tasks require a broader view of the database objects. PostgreSQL stores metadata for views, materialized views, foreign tables, indexes, and partitions in the same system catalogs, so you can adapt the queries above to include them.
4.1. Capture All Table‑Like Objects
SELECT n.nspname AS schema_name,
c.relname AS object_name,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'f' THEN 'foreign table'
ELSE c.relkind::text
END AS object_type,
u.usename AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_user u ON u.usesysid = c.relowner
WHERE n.nspname = 'my_app'
AND c.relkind IN ('r','v','m','f')
ORDER BY n.nspname, c.relname;
The CASE expression normalizes the internal relkind codes (r, v, m, f) into human‑readable labels. You can further refine the IN list to exclude objects you’re not interested in (e.g., omit 'f' if foreign tables are not used) The details matter here..
4. jt. Using \dt+ for Detailed Output
If you’re in psql and need a richer view that includes row estimates, toast status, and tablespace, use the extended meta‑command:
\dt+ my_app
The + modifier adds columns such as Type, Owner, Tablespace, and Description. This is especially handy when you’re preparing documentation or performing capacity planning.
5. Automating Table Discovery
For CI/CD pipelines, monitoring scripts, or automated documentation generators, you can embed the queries directly into shell scripts. Below is a compact Bash snippet that outputs a CSV suitable for import into a spreadsheet or a data‑modeling tool No workaround needed..
#!/usr/bin/env bash
set -euo pipefail
DBNAME="my_app"
PSQL="psql -t -A -d ${DBNAME} -c"
# Use the system‑catalog query for maximum flexibility
"${PSQL}" "
SELECT n.nspname || ',' || c.relname || ',' ||
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'f' THEN 'foreign table'
ELSE '' END || ',' || u.usename
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_user u ON u.usesysid = c.relowner
WHERE n.nspname = 'my_app'
AND c.relkind IN ('r','v','m','f')
ORDER BY n.nspname, c.relname;
" > tables_${DBNAME}.csv
echo "Table list written to tables_${DBNAME}.csv"
Running this script creates a file like:
my_app,users,table,alice
my_app,orders,table,bob
my_app,vw_order_summary,view,alice
my_app,mat_summary,materialized view,bob
You can pipe the CSV into awk, sed, or any downstream tool that consumes a list of schema‑qualified objects.
6 Kassenbestand: Best Practices for Keeping Your Object Inventory Up‑to‑Date
-
put to work Schemas for Isolation – Keep development, test, and production objects in separate schemas (
dev,test,prod). This makes targeted queries trivial and reduces noise in global inventories. -
Document Ownership – The owner column (
relowner) is a quick proxy for responsibility. Ensure ownership is assigned to a role that can grant privileges when needed, and update it when objects are transferred Worth keeping that in mind.. -
Maintain Descriptive Comments – Use
COMMENT ON TABLEto
attach a brief, up‑to‑date description. A comment such as COMMENT ON TABLE orders IS 'Core transactional orders table'; not only documents intent but also surfaces in \d+ output and information_schema, making it easier for new team members to understand purpose without digging through code.
The official docs gloss over this. That's a mistake It's one of those things that adds up..
-
Run Periodic Audits – Schedule the catalog query from Section 3 on a nightly or weekly basis. Compare the results against a baseline CSV or a database of approved objects, and alert on missing, unexpected, or renamed objects. This catches drift caused by manual changes or incomplete migrations That alone is useful..
-
Integrate With Migration Workflows – Generate inventory reports as part of migration testing. Run the catalog query before and after applying a migration and fail if object definitions change unintentionally. This is especially useful in CI/CD pipelines where schema changes should be deliberate and reviewable.
-
Review Ownership and Privileges Together – Ownership changes can affect grants and access. When transferring an object, also review dependent privileges and adjust grants accordingly. Keeping ownership and permissions aligned prevents orphaned objects and unexpected access paths.
Together, these practices turn ad hoc catalog queries into a living object inventory. Which means by combining schema isolation, ownership metadata, descriptive comments, automated audits, and migration-aware checks, teams can maintain a reliable map of PostgreSQL objects. The result is less guesswork, smoother collaboration, and greater confidence when operating production databases Simple, but easy to overlook. But it adds up..