How to show tables in PostgreSQL?

How to List and Describe Tables in PostgreSQL

Learn essential commands to view database tables, their structures, and details in PostgreSQL, enhancing your database management skills.

Navigating and understanding the schema of a PostgreSQL database is a fundamental skill for developers and database administrators alike. Whether you're exploring an unfamiliar database, debugging an application, or simply verifying your data model, knowing how to list tables and inspect their definitions is crucial. This article will guide you through the most common and effective methods to achieve this using the psql command-line interface and SQL queries.

Listing All Tables in the Current Database

The simplest way to see all tables within your currently connected database is by using a psql meta-command. This command is specifically designed for convenience within the psql client and provides a quick overview of your database objects.

\dt

List all tables in the current schema using psql meta-command

The \dt command lists tables, views, and sequences. If you want to see only tables, it's generally sufficient as views and sequences are often listed separately or with different meta-commands. For a more detailed listing, including system tables, you can use \dtS.

Inspecting Table Structure (Schema Description)

Once you have a list of tables, you'll often need to examine their structure, including column names, data types, constraints, and default values. The psql client offers another powerful meta-command for this purpose.

\d your_table_name

Describe the structure of a specific table

Replace your_table_name with the actual name of the table you wish to inspect. This command provides a comprehensive description, including:

• Columns: Name, Type, Collation, Nullable, Default
• Indexes: Primary keys, unique constraints, and other indexes
• Foreign-key constraints: Relationships to other tables
• Triggers: Any associated triggers
• Inherited tables: If the table inherits from another

For example, to describe a table named users:

\d users

Example of describing the 'users' table

Using SQL Queries for Table Information

While psql meta-commands are convenient, sometimes you need to query the system catalogs directly for more programmatic access or to filter results in specific ways. PostgreSQL stores all its metadata in system tables, primarily within the information_schema and pg_catalog schemas.

To list all user-defined tables using a SQL query, you can query the information_schema.tables view. This is a standard SQL way to get schema information and is portable across different SQL databases that implement the information_schema.

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

SQL query to list all user-defined tables

This query filters out system tables by excluding pg_catalog and information_schema schemas. To get column details for a specific table using SQL:

SELECT column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'your_table_name';

SQL query to describe columns of a specific table

Understanding Table Relationships (ER Diagram Concept)

Understanding how tables relate to each other is crucial for complex databases. While psql's \d command shows foreign key constraints for a single table, visualizing the entire database's relationships can be done conceptually with an Entity-Relationship (ER) diagram. Here's a simplified representation of how two tables might relate:

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE-ITEM : contains
    PRODUCT ||--o{ LINE-ITEM : includes
    CUSTOMER { 
        int customer_id PK
        varchar name
        varchar email
    }
    ORDER { 
        int order_id PK
        int customer_id FK
        date order_date
    }
    PRODUCT { 
        int product_id PK
        varchar product_name
        decimal price
    }
    "LINE-ITEM" { 
        int line_item_id PK
        int order_id FK
        int product_id FK
        int quantity
    }

Example Entity-Relationship Diagram for a simple e-commerce database

This ER diagram visually represents the relationships between CUSTOMER, ORDER, PRODUCT, and LINE-ITEM tables, showing primary keys (PK) and foreign keys (FK) and how they link. This helps in grasping the overall database structure at a glance.

Practical Steps for Database Exploration

Here's a summary of the practical steps you can take to explore your PostgreSQL database tables:

1. Connect to your PostgreSQL database

Open your terminal and use the psql command: psql -U your_username -d your_database_name.

2. List all tables

Once connected, type \dt and press Enter to see a list of all tables in the current schema.

3. Describe a specific table

To view the schema of a table, use \d table_name. For example, \d products.

4. Query information_schema for detailed insights

For more advanced queries or scripting, use SELECT statements against information_schema.tables or information_schema.columns.