intro
In this blog, we will look at how to list tables in a Postgres database. The goal is to retrieve a list of tables and gain insights into a PostgreSQL database structure!
When working with databases, it is important to have a clear view of the available tables. Before diving into this concept of listing tables in Postgres, it is important to understand that PostgreSQL organizes tables within schemas. By default, tables are created in the public schema, but databases can contain multiple schemas for better organization. This hierarchical structure influences how tables are queried and listed.
The following sections will focus on how to achieve this task — time to uncover more details!
Listing Tables In Postgres: Four Different Approaches
Postgres offers four different approaches for listing tables in the database. These are:
Let us look at how each of these different approaches works.
Approach #1 — Using the PSQL CLI
The simplest way to list tables in Postgres is using the \dt command in psql and it works by displaying tables in the active schema.
Follow closely the steps below to list tables in a Postgres table with the psql CLI Method:
Step 1: Log in to the Postgres server using the command below:
1
psql -U postgres
This command launches the Postgres interactive terminal, attempts to connect as the Postgres superuser to the default database — usually also named postgres.
If successful, you will see a prompt like this:
1
psql -U postgres
It is worth noting that you can specify any user aside the superuser provided the user has the necessary database permissions.
Step 2: Now, use the \dt command to list all tables in your Postgres database like this:
1
List of relations
2
Schema | Name | Type | Owner
3
--------+-------------------+-------+----------
4
public | accounts | table | postgres
5
public | entries | table | postgres
6
public | schema_migrations | table | postgres
7
public | transfers | table | postgres
8
(4 rows)
Using the PSQL interactive terminal to list Postgres tables
If you need to switch to a different database before listing tables, use:
1
\c database_name;
Then, you can now run \dt to list the tables in your Postgres database. Bingo!
Approach #2 — Using the Information Schema
Another way to list tables in Postgres is to use a SELECT statement to query data from a Postgres system catalog table as below.
Note that the queries below will be executed in DbVisualizer, the world’s leading database client with the highest user satisfaction. In addition to being able to connect to several DBMSs, it offers great features and full support for all database Postgres capabilities. Try DbVisualizer out now!
1
SELECT table_name
2
FROM information_schema.tables
3
WHERE table_schema = 'public' -- replace 'public' with the desired schema name
4
AND table_type = 'BASE TABLE';
In this method, the query retrieves the table names from the public schema and filters out any non-base tables, such as views or foreign tables.
To retrieve tables from a specific schema, replace public with the desired schema name.
Here, it can be seen that the query has returned the list of tables available at database(postgres) > Schemas > public > Tables, which is the exact path for the tables looking on the left side of the DbVisualizer window pane.
Approach #3 — Using the pg_tables View
The pg_tables method deals with the pg_tables view: a part of the pg_catalog system catalog which is Postgres-specific and directly reflects metadata managed by Postgres. It is particularly useful when you need Postgres-specific details about tables, such as ownership, rules, triggers, and indexes.
This method of listing tables in Postgres achieves similar goals as the pg_catalog method earlier discussed but differs in a few factors such as data source, flexibility, and context.
1
SELECT *
2
FROM pg_tables
3
WHERE schemaname = 'public';
It is important to look at the key differences between these two almost similar methods of listing tables in Postgres.
Key Differences Between the Information Schema Method and the pg_tables method
Understanding the differences between these methods can help you choose the most appropriate approach for your needs. Below is a comparison table summarizing their key distinctions.
| Features | Information Schema method | pg_catalog method |
|---|---|---|
| Data scope | Focused on Postgres metadata like indexes and triggers. | General table metadata (schema, name, type). |
| Flexibility | Returns Postgres-specific details like ownership and triggers. | More generic, suited for schema-wide or cross-database use. |
| Use cases | Best for Postgres-specific tasks requiring detailed metadata. | Best for cross-platform tasks or filtering by table type. |
As seen in the table above, pg_tables is more suited for Postgres-specific metadata, while information_schema follows SQL standards and provides broader affinity for general use cases.
Approach #4 — Using a Database Client like DbVisualizer
The simplest and most convenient way to display all tables in Postgres is by using a database client like DbVisualizer. This amazing tool provides everything needed to visually connect to and manage Postgres tables.
DbVisualizer is a database client that offers enhanced support for Postgres-specific object types and functionalities. To list tables in Postgres using DbVisualizer, follow these steps:
Step #1: Create a Postgres database connection as explained in the official documentation here.
Step #2: Right-click on the created Postgres database connection and choose “Connect” from the menu:
Step #3: Open your Postgres database connection tree. Next, click on your database tree, then Schemas, and then on your public tree. Finally, click on Tables and you should have the list of the available tables in your Postgres database like this:
This method is significantly simpler and more intuitive than running commands or queries in the terminal. Even those without technical expertise can navigate it effortlessly.
List your Postgres tables with DbVisualizer in just a few clicks!
Great! Now, it is a wrap!
Conclusion
Unlike MySQL, PostgreSQL does not have a SHOW TABLES command. Yet, Postgres offers a few other ways to list tables in a database. One of the most useful of them involves using a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including listing tables.
Download DbVisualizer for free now! It will surprise you with many things. Take an expo of all of our features, the Pro version of DbVisualizer is free for 21 days.
FAQ
Can I see the data within a table using a show tables Postgres approach?
No, the \dt command in psql primarily focus on displaying metadata about tables. This includes information like table names, schema, columns, data types, and constraints. To view the actual data stored within a table, you need to use the SELECT statement.
Is there a SHOW TABLES command in Postgres?
No, the SHOW TABLES command in Postgres does not exist. It exists in MySQL, but not in Postgres.
Why use a database client to list tables in Postgres?
The simplest method to view tables in Postgres is by using a database client. This is because a Postgres client provides a user-friendly interface that displays all the tables on your Postgres server. This method eliminates the need for terminal queries or commands, making it an ideal solution for both beginners and experienced users.
