How to Show Tables in PostgreSQL

In PostgreSQL, unlike MySQL, there isn't a direct SHOW TABLES command. However, PostgreSQL offers several methods to list tables within a database, catering to different needs and preferences. This guide explores various approaches to display tables in PostgreSQL using the psql command-line tool and SQL queries. (PostgreSQL – Show Tables - GeeksforGeeks)

Using psql Meta-Commands

The psql tool provides meta-commands (prefixed with a backslash) to interact with the database. (How to List databases and tables in PostgreSQL using psql - Atlassian)

1. List All Tables in the Current Schema

   After connecting to your desired database, use:

   \dt
   

   This command lists all tables in the public schema by default.

2. List All Tables in All Schemas

   To view tables across all schemas:

   \dt *.*
   

3. Detailed Table Information

   For additional details like table size and description:

   \dt+
   

   Or for all schemas:

   \dt+ *.*
   

4. List All Database Objects

   To display tables, views, sequences, and more:

   \d
   

   This provides a comprehensive overview of all relations in the current schema.

Querying System Catalogs

PostgreSQL's system catalogs store metadata about database objects. (PostgreSQL – Show Tables - GeeksforGeeks)

1. Using pg_catalog.pg_tables

   To list user-defined tables:

   SELECT * FROM pg_catalog.pg_tables
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

   This query excludes system tables, focusing on user-created ones.

2. Using information_schema.tables

   The information_schema is a standardized schema providing metadata:

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

   This approach is beneficial for cross-database compatibility.

Using GUI Tools

For those preferring graphical interfaces:

• pgAdmin: Navigate through the object browser to view tables within schemas.
• TablePlus: Provides a sidebar listing all tables in the connected database.
• Beekeeper Studio: Offers a user-friendly interface to browse and manage tables.

Conclusion

While PostgreSQL doesn't have a direct SHOW TABLES command, it offers versatile methods to list tables: (PostgreSQL – Show Tables - GeeksforGeeks)

• Use psql meta-commands like \dt for quick access.
• Query system catalogs (pg_catalog.pg_tables) for detailed metadata.
• Leverage information_schema.tables for standardized, cross-database queries.
• Utilize GUI tools for a visual approach to database management. (How to List databases and tables in PostgreSQL using psql - Atlassian, PostgreSQL Show Tables - Neon, PostgreSQL – Show Tables - GeeksforGeeks)

Choose the method that best fits your workflow and preferences.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ