A Comprehensive Guide to Listing All Tables in PostgreSQL

Overview of PostgreSQL Table Listing Methods

Listing database tables is a fundamental operation in PostgreSQL database management. Depending on specific use cases and technical requirements, multiple approaches can be employed. This article systematically introduces three primary methods: psql meta-commands, INFORMATION_SCHEMA queries, and system catalog table queries.

Using psql Meta-commands

PostgreSQL's interactive terminal psql provides a set of convenient meta-commands, with \dt specifically designed for listing tables in the current database. The basic usage is as follows:

\c liferay
\dt

The above commands first connect to the liferay database, then display all tables within that database. To list all tables across all schemas, use:

\dt *.*

This approach includes system tables (such as those in pg_catalog and information_schema). To view only tables in user-defined schemas, you can limit the search scope by setting the search_path.

INFORMATION_SCHEMA Standard Queries

INFORMATION_SCHEMA is a set of system views defined by the SQL standard, providing standardized access to database metadata. To list tables in a specific schema, use the following query:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

The advantage of this method lies in its compliance with SQL standards, offering excellent portability. The query results include only user tables, excluding system tables, making it more suitable for most application scenarios.

Direct System Catalog Table Queries

For scenarios requiring PostgreSQL-specific information, you can directly query system catalog tables. Using the psql -E command reveals the actual SQL queries executed internally by psql:

$ psql -E regress
regress=# \list

This displays the SQL query actually run by psql when executing the \list command. For table listing, the relevant system catalog query is:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Method Comparison and Selection Recommendations

Each of the three methods has distinct advantages and limitations:

• psql Meta-commands: Simple to use, ideal for interactive sessions, but difficult to use programmatically
• INFORMATION_SCHEMA: Compliant with SQL standards, excellent portability, suitable for cross-database applications
• System Catalog Tables: Provides the most detailed information, appropriate for scenarios requiring PostgreSQL-specific features

In practical applications, it's recommended to choose the appropriate method based on specific requirements. For simple table listing, the \dt command is most convenient; for programmatic access, INFORMATION_SCHEMA is the preferred choice; and when PostgreSQL-specific metadata is needed, consider directly querying system catalog tables.