How to List Tables in PostgreSQL (psql, SQL & Schema Examples)

Table of Contents

• Ways to List Tables in PostgreSQL
• How to List Tables Using psql
• How to List Tables Using SQL Queries
• How to List Tables in a Specific Database
• Common Issues When Listing Tables in PostgreSQL
• Best Practices for Listing Tables in PostgreSQL
• Final Thoughts
• FAQs

When working with PostgreSQL databases, one of the most common tasks developers perform is checking which tables exist in a database. Whether you’re debugging, exploring a new schema, or validating migrations, knowing how to list tables in PostgreSQL quickly can save a lot of time.

In this blog, you’ll learn multiple ways to list tables using psql commands and SQL queries, along with common mistakes and best practices.

Ways to List Tables in PostgreSQL

There are two primary approaches to listing tables in PostgreSQL:

• Using psql commands (best for quick checks).
• Using SQL queries (best for scripts and automation).

Both methods are useful depending on your workflow.

How to List Tables Using psql

The psql command-line interface provides simple shortcuts to inspect database objects.

Show All Tables in psql

• \dt

This command lists all tables in the current database and schema (usually public).

This is the fastest way to postgresql list of tables when you’re working locally.

List Tables in a Specific Schema

• \dt schema_name.*

Example:

• \dt public.*

If your tables are not in the default schema, this command helps avoid confusion when tables seem to be missing.

Show Tables with Extended Information

• \dt+

This displays additional details like table size and descriptions, which is helpful when analyzing large databases.

How to List Tables Using SQL Queries

If you’re working in applications, dashboards, or automation scripts, SQL queries are more flexible than psql commands.

List Tables Using information_schema

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

This method is portable and works across different database tools.

PostgreSQL exposes table metadata through the official PostgreSQL documentation on information_schema, which is useful when working across different schemas and environments.

List Tables Using pg_catalog

• SELECT tablename
FROM pg_tables
WHERE schemaname = 'public';

This approach is PostgreSQL-specific but faster for system-level queries.

How to List Tables in a Specific Database

Before listing tables, make sure you are connected to the correct database.

• \c database_name

After connecting, run:

• \dt

Many issues related to postgresql show tables happen because developers are connected to the wrong database.

If you’re building scripts or tools, SQL queries are more reliable than interactive commands.

Common Issues When Listing Tables in PostgreSQL

Here are a few reasons why tables may not appear:

• Tables exist in a different schema.
• Missing permissions for the current user.
• Connected to the wrong database.

If you also manage users, reviewing postgresql list users and permissions can help resolve access issues.

Best Practices for Listing Tables in PostgreSQL

• Always confirm the active database.
• Specify schemas explicitly.
• Use SQL queries for repeatable tasks.
• Avoid relying only on the public schema.

These best practices align well with software development best practices and help prevent environment-related mistakes.

Final Thoughts

Knowing how to list tables in PostgreSQL is a foundational skill for anyone working with relational databases. Use psql commands for quick inspections and SQL queries when working with scripts or applications. By understanding schemas and permissions, you will avoid common pitfalls and work more efficiently.

FAQs

1. How do I list tables in PostgreSQL using psql?

Use the command:

• \dt

This lists tables in the current schema.

2. Why can’t I see tables in PostgreSQL?

You may be connected to the wrong database, using the wrong schema, or lacking permissions.

3. How do I list tables in all schemas?

• SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';