NeuroAgent

PostgreSQL Show Tables Equivalent: Complete Guide

Learn how to list tables in PostgreSQL with multiple methods including information schema queries, psql commands, and system catalog queries. Complete guide with examples for MySQL users.

#database-administration #database-metadata #information-schema #mysql-migration #postgresql-tables #psql-commands #system-catalogs

10/24/2025, 10:51 PM

Question

How to list tables in PostgreSQL? What is the equivalent command to MySQL’s SHOW TABLES in PostgreSQL?

NeuroAgent

In PostgreSQL, there is no direct equivalent to MySQL’s SHOW TABLES command. Instead, you can list tables using several approaches including querying the information schema with SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';, using the \dt command in psql, or querying the pg_catalog tables directly like SELECT tablename FROM pg_tables WHERE schemaname = 'public';.

Getting Started with PostgreSQL Table Listing
Using the Information Schema (Standard SQL Method)
PSQL Command Line Interface Methods
Querying System Catalogs Directly
Filtering and Sorting Results
Practical Examples and Use Cases
Common Issues and Troubleshooting

Getting Started with PostgreSQL Table Listing

PostgreSQL differs from MySQL in its approach to metadata management. While MySQL provides the straightforward SHOW TABLES command, PostgreSQL offers multiple ways to retrieve table information, each with different advantages. The most common methods include using the standard SQL information schema, leveraging the psql meta-commands, or directly querying system catalogs.

Understanding these methods is essential for database administrators and developers working with PostgreSQL. Each approach serves different use cases - from quick command-line checks to programmatic table discovery in applications.

Key Point: PostgreSQL’s design prioritizes standards compliance and extensibility, which is reflected in its multiple approaches to metadata retrieval rather than a single simple command.

Using the Information Schema (Standard SQL Method)

The information schema is the standard SQL way to access database metadata. This method is portable across different SQL databases and is recommended for applications that need to work with multiple database systems.

sql

-- List all tables in the current schema
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_type = 'BASE TABLE';

This query returns the names of all base tables in the public schema. The information_schema.tables view contains metadata about all tables in the database, and by filtering on table_schema = 'public', you focus on the default user schema.

For a more comprehensive view, you can include additional columns:

sql

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

This provides not just table names but also their types, schemas, and ownership information. The exclusion of system schemas (pg_catalog and information_schema) ensures you only see user-defined tables.

The information schema approach is ANSI SQL compliant, making it the most portable solution for cross-database applications.

PSQL Command Line Interface Methods

When working directly with the PostgreSQL command-line interface (psql), you have several convenient meta-commands for listing tables:

Basic Table Listing

bash

\dt

This command lists all tables in the current schema, along with their owners, table type, and size information.

Listing Tables with Descriptions

bash

\dt+

The + modifier provides additional information including indexes, storage parameters, and comments.

Listing Tables in All Schemas

bash

\dt *.*

This shows tables across all schemas, not just the current one.

Filtering by Pattern

bash

\dt pattern*

You can use wildcards to filter tables by name pattern, for example \dt users* would show all tables starting with “users”.

Example usage:

bash

# List all tables in the current schema
postgres=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner   
--------+----------------+-------+----------
 public | users          | table | postgres
 public | products       | table | postgres
 public | orders         | table | postgres
(3 rows)

# List tables with additional information
postgres=# \dt+
                                  List of relations
 Schema |      Name      | Type  |  Owner   |    Size    | Description 
--------+----------------+-------+----------+------------+-------------
 public | users          | table | postgres | 16 kB      | 
 public | products       | table | postgres | 8192 kB    | 
 public | orders         | table | postgres | 32 kB      | 
(3 rows)

These meta-commands are particularly useful for interactive database exploration and administration.

Querying System Catalogs Directly

PostgreSQL’s system catalogs contain detailed metadata about the database. While the information schema provides a standardized view, querying the catalogs directly can be more efficient for specific use cases.

Using pg_tables

sql

SELECT tablename 
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY tablename;

The pg_tables view provides a convenient interface to the underlying catalog tables. This is often faster than querying the information schema for simple table listings.

Using pg_class

sql

SELECT relname 
FROM pg_class 
WHERE relkind = 'r' 
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY relname;

This query uses the more fundamental pg_class catalog, which contains information about database objects. The relkind = 'r' filter specifically selects regular tables (relations).

Comprehensive Catalog Query

sql

SELECT 
    n.nspname as schema_name,
    c.relname as table_name,
    pg_get_userbyid(c.relowner) as owner,
    pg_size_pretty(pg_total_relation_size(c.oid)) as size,
    obj_description(c.oid) as comment
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' 
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;

This advanced query provides comprehensive table information including size and comments by joining multiple catalog tables.

Direct catalog queries are generally faster but less portable than information schema queries. Use them when performance is critical and you’re working exclusively with PostgreSQL.

Filtering and Sorting Results

In real-world scenarios, you often need to filter and organize the table listing for better usability.

Filtering by Schema

sql

-- Tables in specific schemas
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema IN ('public', 'analytics', 'reporting')
  AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Filtering by Table Name Pattern

sql

-- Tables matching a pattern
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public'
  AND table_name LIKE 'user%'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

Filtering by Table Size

sql

-- Tables larger than specific size
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'::name||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
  AND pg_total_relation_size(schemaname||'.'::name||tablename) > 1000000
ORDER BY pg_total_relation_size(schemaname||'.'::name||tablename) DESC;

Getting Table Statistics

sql

-- Tables with row counts
SELECT 
    schemaname,
    tablename,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    n_live_tup as live_rows,
    n_dead_tup as dead_rows
FROM pg_stat_user_tables 
WHERE schemaname = 'public'
ORDER BY tablename;

These filtering techniques help you focus on specific tables of interest and organize the output in meaningful ways.

Practical Examples and Use Cases

Application Integration

For applications that need to dynamically discover tables:

python

# Python example using psycopg2
import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cursor = conn.cursor()

cursor.execute("""
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'public' 
      AND table_type = 'BASE TABLE'
    ORDER BY table_name
""")

tables = [row[0] for row in cursor.fetchall()]
print("Available tables:", tables)

Database Documentation Script

bash

#!/bin/bash
# Generate database documentation

psql -d your_database -c "
SELECT 
    '## Schema: ' || table_schema || chr(10) ||
    '### Tables in ' || table_schema || chr(10) ||
    STRING_AGG(
        '- **' || table_name || '**' || 
        CASE WHEN table_comment IS NOT NULL 
             THEN ' - ' || table_comment 
             ELSE '' 
        END, 
        chr(10)
    ) as tables_list
FROM (
    SELECT 
        table_schema,
        table_name,
        obj_description(
            ('"' || table_schema || '"."' || table_name || '"')::regclass::oid,
            'pg_class'
        ) as table_comment
    FROM information_schema.tables 
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
      AND table_type = 'BASE TABLE'
) t
GROUP BY table_schema
ORDER BY table_schema;
" > database_documentation.md

Monitoring Script for New Tables

bash

#!/bin/bash
# Check for new tables since last check

LAST_CHECK_FILE="/tmp/last_tables_check"
CURRENT_DB="your_database"

if [ -f "$LAST_CHECK_FILE" ]; then
    OLD_TABLES=$(cat "$LAST_CHECK_FILE")
    CURRENT_TABLES=$(psql -d "$CURRENT_DB" -t -c "
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
          AND table_type = 'BASE TABLE'
        ORDER BY table_name
    " | tr '\n' ' ')
    
    NEW_TABLES=$(comm -13 <(echo "$OLD_TABLES" | tr ' ' '\n' | sort) <(echo "$CURRENT_TABLES" | tr ' ' '\n' | sort))
    
    if [ -n "$NEW_TABLES" ]; then
        echo "New tables detected: $NEW_TABLES"
        # Send notification or take action
    fi
else
    echo "First run - storing baseline"
fi

psql -d "$CURRENT_DB" -t -c "
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'public' 
      AND table_type = 'BASE TABLE'
    ORDER BY table_name
" > "$LAST_CHECK_FILE"

These examples demonstrate how to integrate PostgreSQL table listing into various automation and monitoring scenarios.

Common Issues and Troubleshooting

Permission Issues

If you encounter permission errors when trying to list tables:

sql

-- Check your current user and role
SELECT current_user, session_user;

-- Grant necessary permissions if needed
GRANT USAGE ON SCHEMA public TO your_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;

Tables Not Showing Up

If expected tables don’t appear in your listing:

Check the schema: Tables might be in a different schema than expected

sql

-- List all schemas
SELECT schema_name FROM information_schema.schemata;

-- List tables in all schemas
SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Check table type: Views might be filtered out

sql

-- Include views
SELECT table_name, table_type 
FROM information_schema.tables 
WHERE table_schema = 'public';

Check for temporary tables: They might be in a different location
sql
```
-- List temporary tables
SELECT * FROM pg_temp_tables;
```

Performance Considerations

For large databases, table listing can be slow. Optimize with:

sql

-- Use indexes for better performance on system catalogs
CREATE INDEX IF NOT EXISTS idx_pg_tables_schemaname ON pg_tables(schemaname, tablename);

-- Cache results in applications when possible
-- Use materialized views for frequently accessed metadata
CREATE MATERIALIZED VIEW mv_table_list AS
SELECT table_name, table_schema, table_type
FROM information_schema.tables
WITH DATA;

-- Refresh periodically
REFRESH MATERIALIZED VIEW mv_table_list;

Migration from MySQL

When migrating from MySQL, create a helper function:

sql

CREATE OR REPLACE FUNCTION show_tables() 
RETURNS TABLE(table_name text) AS $$
BEGIN
    RETURN QUERY 
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'public' 
      AND table_type = 'BASE TABLE'
    ORDER BY table_name;
END;
$$ LANGUAGE plpgsql;

-- Now you can use:
SELECT * FROM show_tables();

This provides a familiar interface for MySQL users while maintaining PostgreSQL’s native capabilities.

Conclusion

PostgreSQL offers multiple robust methods for listing tables, each suited to different use cases. The information schema provides ANSI SQL standard compliance, psql meta-commands offer convenient interactive exploration, and direct catalog queries deliver maximum performance. Understanding these alternatives to MySQL’s SHOW TABLES command is essential for effective PostgreSQL database management.

Key recommendations:

Use SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE'; for portable SQL applications
Use \dt in psql for quick interactive exploration
Use direct catalog queries like SELECT tablename FROM pg_tables WHERE schemaname = 'public'; for performance-critical applications
Implement proper filtering and sorting for production environments
Create helper functions or views to accommodate MySQL migration patterns

Mastering these table listing techniques will significantly improve your PostgreSQL workflow and help you efficiently manage database metadata across different scenarios.

Sources

How to list all tables in PostgreSQL including system tables?What's the difference between information_schema and pg_catalog in PostgreSQL?How to list tables in a specific schema in PostgreSQL?How to list tables with their sizes in PostgreSQL?How to list tables in PostgreSQL using Python?How to create a function to mimic MySQL's SHOW TABLES in PostgreSQL?

Ask NeuroAgent

PostgreSQL Show Tables Equivalent: Complete Guide

Contents

Getting Started with PostgreSQL Table Listing

Using the Information Schema (Standard SQL Method)

PSQL Command Line Interface Methods

Basic Table Listing

Listing Tables with Descriptions

Listing Tables in All Schemas

Filtering by Pattern

Querying System Catalogs Directly

Using pg_tables

Using pg_class

Comprehensive Catalog Query

Filtering and Sorting Results

Filtering by Schema

Filtering by Table Name Pattern

Filtering by Table Size

Getting Table Statistics

Practical Examples and Use Cases

Application Integration

Database Documentation Script

Monitoring Script for New Tables

Common Issues and Troubleshooting

Permission Issues

Tables Not Showing Up

Performance Considerations

Migration from MySQL

Conclusion

Sources