How to Show Tables in PostgreSQL Using psql

August 25, 2024 By Matthew Rathbone

Prerequisites

PostgreSQL installed on your system.
Access to the psql command-line tool.
A basic understanding of SQL and PostgreSQL.

Connecting to the Database

First, you’ll need to connect to your PostgreSQL database. You can do this by running the psql command followed by your database name:

psql -d your_database_name

If your PostgreSQL server requires a username and password, you can include the username as follows:

psql -U your_username -d your_database_name

After entering your password, you should be connected to your database.

Using the `\dt` Command

The quickest way to list all tables in your current database is by using the \dt command:

\dt

Expected Output:

               List of relations
 Schema |        Name         | Type  |  Owner  
--------+---------------------+-------+---------
 public | my_first_table      | table | postgres
 public | my_second_table     | table | postgres
 public | another_table       | table | postgres
(3 rows)

If you want to see more information about your tables you can also use:

\dt+

This command shows all the tables in the public schema. If you want to list tables in all schemas, use:

\dt *.*

Querying the Information Schema

Another approach is to directly query the information_schema.tables view. This is especially useful if you want more control over the output:

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

Expected Output:

 table_schema |    table_name    
--------------+------------------
 public       | my_first_table
 public       | my_second_table
 public       | another_table
(3 rows)

This query lists all tables in all schemas except for system schemas.

Using the `pg_tables` View

You can also list tables using the pg_tables system catalog:

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

Expected Output:

 schemaname |    tablename    
------------+-----------------
 public     | my_first_table
 public     | my_second_table
 public     | another_table
(3 rows)

This method is very similar to querying the information_schema.tables but directly references PostgreSQL’s internal catalog.

Filtering Tables by Schema

If you want to list tables from a specific schema, you can modify the previous queries to filter by the table_schema or schemaname column:

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

Or using pg_tables:

SELECT tablename
FROM pg_tables
WHERE schemaname = 'your_schema_name';

Conclusion

Listing tables in PostgreSQL using psql is straightforward with multiple methods available depending on your needs. The \dt command is the quickest, while querying the information_schema or pg_tables offers more control and flexibility. Use the methods that best suit your workflow.

Other articles you may enjoy: