How to Show Tables in PostgreSQL

Key Takeaways

PostgreSQL uses \dt in psql to list tables instead of SHOW TABLES.
System catalogs and information_schema offer detailed, query-based alternatives.
GUI tools like pgAdmin simplify table browsing for non-command-line users.

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)

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.
List All Tables in All Schemas

To view tables across all schemas:
```
\dt *.*
```
Detailed Table Information

For additional details like table size and description:
```
\dt+
```
Or for all schemas:
```
\dt+ *.*
```
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)

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.

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)