How to Show Tables in PostgreSQL: Step-by-Step Guide

This guide explains how to list tables in your PostgreSQL database using simple commands and SQL queries. Perfect for beginners!

1. Using the \dt Command in psql (Easiest Way)

The psql command-line tool has a quick command for listing tables.

Explanation: The \dt is a psql meta-command that directly queries the system catalogs to display a formatted list of tables. It’s the quickest way to see tables if you’re already in the psql shell.

Steps:

1. Open your terminal or command prompt.
2. Connect to your PostgreSQL database:

   psql -U your_username -d your_database_name

3. List tables in the current database:

   \dt

Example Output:

This command shows tables in the current schema (usually public).

             List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres
 public | orders    | table | postgres
 public | products  | table | postgres
(3 rows)

2. Using SQL Queries (Standard Way)

You can also use standard SQL to list tables by querying PostgreSQL’s system catalogs .

Explanation: These methods query specific system views (pg_tables or information_schema.tables) to retrieve table information. They offer more flexibility for filtering and are useful in scripts or when not using the psql shell directly.

Option A: Query pg_tables (PostgreSQL Specific)

Explanation: pg_tables is a PostgreSQL-specific view that lists tables. It’s often faster and provides direct access to PostgreSQL’s internal table information.

List tables in the public schema:

SELECT tablename
FROM pg_tables
WHERE schemaname = 'public';

Example Output:

 tablename
------------
 customers
 orders
 products
(3 rows)

List tables in any schema (shows schema name too):

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); -- Exclude system tables 

Example Output:

 schemaname | tablename
------------+------------
 public     | customers
 public     | orders
 public     | products
 sales      | invoices
(4 rows)

Option B: Query information_schema.tables (Standard SQL)

Explanation: information_schema.tables is part of the SQL standard and provides a portable way to get table information across different database systems . Using table_type = 'BASE TABLE' ensures you only see actual tables, not views.

List tables in the public schema using the standard schema:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE'; -- Ensures we only get tables, not views

Example Output:

 table_name
------------
 customers
 orders
 products
(3 rows)

List tables in any user-defined schema:

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

Example Output:

 table_schema | table_name
--------------+------------
 public       | customers
 public       | orders
 public       | products
 sales        | invoices
(4 rows)

3. Listing Tables in Specific Schemas

If your database uses multiple schemas, you can filter the results.

Explanation: By changing the schemaname or table_schema condition in your query, you can focus on tables within a particular schema.

Using pg_tables:

SELECT tablename
FROM pg_tables
WHERE schemaname = 'your_custom_schema_name';

Using information_schema:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_custom_schema_name'
AND table_type = 'BASE TABLE';

4. Filtering Tables by Name

If you have many tables, you can search for specific ones.

Explanation: Using the LIKE operator allows you to find tables whose names match a specific pattern.

Using psql backslash command:

\dt *user*  -- Lists tables with 'user' anywhere in the name

Using SQL (pg_tables):

SELECT schemaname, tablename
FROM pg_tables
WHERE tablename LIKE '%user%'; -- Finds tables with 'user' in the name

Example Output:

 schemaname | tablename
------------+------------
 public     | users
 public     | user_sessions
 logs       | user_activity_log
(3 rows)

Using SQL (information_schema):

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE 'prod_%' -- Finds tables starting with 'prod_'
AND table_type = 'BASE TABLE';

Example Output:

 table_schema | table_name
--------------+------------
 public       | prod_catalog
 inventory    | prod_stock
(2 rows)

---

Quick Reference (Code Examples)

Need a quick reminder? Here are the essential commands with brief explanations:

• List tables in current psql database/schema:

  \dt

  (Uses psql’s built-in command for a quick view )
• List tables in public schema (pg_tables):

  SELECT tablename FROM pg_tables WHERE schemaname = 'public';

  (Queries PostgreSQL’s specific catalog view )
• List tables in public schema (information_schema):

  SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

  (Uses the SQL-standard information schema for portability )
• List tables in any user schema (pg_tables):

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

  (Shows tables from all non-system schemas )
• List tables in any user schema (information_schema):

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

  (Shows tables from all non-system schemas using standard SQL )
• List tables with names containing ‘log’ (pg_tables):

  SELECT tablename FROM pg_tables WHERE tablename LIKE '%log%';

  (Filters tables by name pattern)
• Switch database in psql:

  \c your_database_name

  (Changes your active database connection within psql)

---

Common Issues & Solutions

• No tables listed?
  • Check you are connected to the correct database: \c correct_database_name
  • Check if you are looking in the right schema.
• Permission errors?
  • You might not have access to view certain schemas or tables. Contact your DBA.
• Too many tables?
  • Use the LIKE operator in your SQL query to filter names (see Filtering examples above).
  • Or, in psql, use \dt pattern (e.g., \dt user*).

---

Conclusion

Listing tables in PostgreSQL is straightforward using either the \dt command in psql or standard SQL queries against system catalogs . Use the method that best fits your workflow. The code examples above provide ready-to-use snippets for common scenarios.