How to List Tables in All Schemas in PostgreSQL: Complete Guide

Overview of PostgreSQL Table Listing

In PostgreSQL database management, viewing available tables is a common operational requirement. By default, the \dt command only displays tables in the current schema (typically public). However, practical applications often require viewing table lists from all schemas or specific schemas. This article systematically introduces multiple implementation methods, including psql commands, SQL queries, and regular expression pattern matching.

Using psql Commands to List Tables

PostgreSQL's interactive terminal psql provides convenient commands for quickly viewing table information. The basic \dt command shows tables in the current schema, but by adding schema qualifiers, its functional scope can be expanded.

Viewing Tables in All Schemas

To list all tables in all schemas, use the following command:

\dt *.*

This command returns information for all tables in all schemas of the database, including table names, belonging schemas, types, and owners. The asterisk (*) acts as a wildcard here, matching all schema names and table names.

Viewing Tables in Specific Schemas

If you only need to view tables in a specific schema, use the schema name qualifier:

\dt public.*

Replace public with the actual schema name to view all tables in that schema. This method is particularly useful in database environments with multiple schemas, allowing quick location of tables in specific business modules.

Using Regular Expressions for Pattern Matching

PostgreSQL's psql commands support using regular expressions for more flexible pattern matching, but special character processing rules must be noted.

Basic Regular Expression Syntax

Parentheses and vertical bar characters can be used for pattern selection:

\dt (public|s).(s|t)

This command will list tables named s or t in the public or s schemas. The output will display matching tables and their detailed information.

Special Character Processing Rules

In table pattern matching, certain characters have special meanings:

• The dot (.) is interpreted as a separator between schema names and table names
• The asterisk (*) is converted to .* in regular expressions
• The question mark (?) is converted to .
• The dollar sign ($) is matched literally

If you need to use these characters literally, you can use escape sequences: use ? for ., (R+|) for R*, and (R|) for R?.

Alternative Methods Using SQL Queries

In addition to psql commands, table information can also be obtained by querying system catalogs through SQL, which is more flexible in programming and scripting.

Querying information_schema

PostgreSQL follows SQL standards and provides information_schema to store database metadata:

SELECT * FROM information_schema.tables 
WHERE table_schema = 'public'

This query returns all table information in the specified schema. The advantage of information_schema lies in its standardization, making it suitable for applications requiring cross-database platform compatibility.

Querying pg_catalog System Catalog

The PostgreSQL-specific pg_catalog schema provides more detailed table metadata:

SELECT * FROM pg_catalog.pg_tables
WHERE schemaname = 'public'

This method returns more comprehensive information than information_schema, including tablespace, storage parameters, and other PostgreSQL-specific attributes.

Method Comparison and Selection Recommendations

Different table listing methods have their own advantages and disadvantages, and appropriate methods should be selected based on specific requirements.

Interactive Usage Scenarios

For interactive database management, psql commands are most convenient:

• \dt *.* for quick viewing of all tables
• \dt schema_name.* for viewing tables in specific schemas
• Regular expression pattern matching provides flexible filtering capabilities

Programming and Scripting Scenarios

In applications or scripts, SQL queries are more appropriate:

• information_schema is suitable for scenarios requiring standard SQL compatibility
• pg_catalog provides PostgreSQL-specific detailed information
• Complex filtering conditions can be implemented through WHERE clauses

Performance Considerations

In large database environments, querying system catalogs may have performance impacts. Recommendations include:

• For frequent queries, consider caching results
• Use specific schema names to limit query scope
• Avoid querying all schemas when unnecessary

Practical Application Examples

Assuming an e-commerce database containing three schemas: public, inventory, and sales. Here are some practical query examples:

Viewing All User Tables

SELECT table_name, table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_type = 'BASE TABLE';

Counting Tables by Schema

SELECT schemaname, COUNT(*) as table_count
FROM pg_tables
WHERE schemaname NOT LIKE 'pg_%'
GROUP BY schemaname
ORDER BY table_count DESC;

Best Practices and Considerations

When using table listing functionality, the following considerations should be noted:

Permission Management

Ensure users have permissions to access corresponding schemas and data dictionaries. Different users may only see schemas and tables they have permission to access.

Schema Search Path

Understand current search path settings, as this affects table and function references without schema qualifiers. Use SHOW search_path to view current settings.

Performance Optimization

In database environments with numerous schemas and tables, consider using more specific query conditions to limit result set size and improve query efficiency.

By mastering these methods, database administrators and developers can more effectively manage and explore PostgreSQL database structures, improving work efficiency and system maintainability.