How to List Tables in Different Schemas (PostgreSQL)
- PostgreSQL: A powerful open-source object-relational database management system (DBMS).
- Schema: A logical grouping of database objects (tables, views, sequences, etc.) that helps organize and manage them.
- psql: The command-line interface (CLI) tool for interacting with PostgreSQL servers.
Steps:
Connect to your PostgreSQL server:
List tables using
pg_catalog.pg_tables
system catalog view:
Explanation:
information_schema.tables
: This built-in view provides information about all tables in the current database, including their schema names.table_name
: This column holds the name of each table.WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
: This condition filters out tables belonging to the system schemas (pg_catalog
contains system tables andinformation_schema
contains metadata tables).
Output:
The query will display a list of table names, one per row, belonging to user-defined schemas in the database.
Additional Notes:
- You can modify the query to list specific columns by adding them to the
SELECT
clause (e.g.,SELECT table_name, table_schema
). - To list tables from a particular schema, replace
NOT IN ('pg_catalog', 'information_schema')
with= 'your_schema_name'
.
SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
Listing tables from a specific schema (replace your_schema_name
with the actual schema name):
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema_name';
- This code modifies the previous query to include a specific schema name (
your_schema_name
) in theWHERE
clause. - This will only list tables that belong to the specified schema.
Remember:
- Replace
<username>
and<database_name>
in the connection command with your actual credentials. - Execute these code snippets within the
psql
client after connecting to your PostgreSQL server.
This method leverages the built-in \dt
command in the psql
client. It offers flexibility with wildcards for schema and table names.
psql -U <username> <database_name>
# List all tables from all schemas:
\dt *.*
# List all tables from a specific schema (replace 'public' with your schema name):
\dt public.*
# Use regular expressions for more complex filtering (refer to psql documentation for details):
\dt (schema1|schema2).(table_pattern) # List tables matching pattern from schema1 or schema2
Using pg_tables system catalog view (psql):
This method employs the pg_tables
system catalog view similar to the previous approach but provides more control over the output columns.
SELECT table_name, table_schema, table_type -- Add or remove columns as needed
FROM pg_catalog.pg_tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
Information schema views (psql):
PostgreSQL offers several information schema views for introspection. While not as widely used for listing tables, they provide a different perspective:
information_schema.schemata
: Lists all schemas in the database.information_schema.tables
: Lists all tables in the database (including system schemas).
You can combine these views with filtering to achieve similar results. However, the information_schema.tables
approach requires filtering system schemas as shown previously.
Choosing the Right Method:
- If you prefer a simple command-line approach with wildcards,
\dt
is a good choice. - If you need more granular control over the output columns or prefer a pure SQL approach, the
information_schema.tables
method with filtering is ideal. - Information schema views might be useful for exploring schema information beyond just tables.
postgresql command psql