Before you keep reading, I just wanted to let you know about our fast, modern PostgreSQL GUI, Beekeeper Studio. It lists every table in your database in its sidebar, grouped by schema, no \dt required, and it’s free to use for PostgreSQL, download it here.
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:
Beekeeper Studio Is A Free & Open Source PostgreSQL GUI
This is the PostgreSQL client I’ve been looking for! The UI is what sets it apart: Clean, intuitive, powerful. Goodbye clunky PGAdmin. - David Cyprian
Beekeeper Studio is a fast, intuitive and easy to use PostgreSQL client. It supports loads of other databases too, and works great on Windows, Mac and Linux.
What PostgreSQL Users Say About Beekeeper Studio
Unlike pgAdmin, Beekeeper is remarkably simple to use yet not lacking in features–and the price is right! The database backup feature is particularly useful. Just great software.
Fabulous tool, the most practical IDE I’ve found to date for Postgres and MySql db admin - it just works, no fuss :-)