PostgreSQL By Example: Show Tables with PSQL

Navigating your PostgreSQL database requires a keen understanding of its structure, including how to show tables using the command line interface, psql. In this guide, we’ll walk you through this crucial task, demonstrating how to easily and efficiently display and understand your database tables. Whether you’re a beginner or experienced user, mastering this skill is vital for managing and operating PostgreSQL databases.

PostgreSQL Command to Show All Tables in Current Schema

To interact with a PostgreSQL database and display its tables, you’ll first need to connect to the desired database. Once connected, you can use the SQL command \dt in the PostgreSQL interactive terminal to show all tables within the database.

To do this, follow these simple steps:

1. Open a Terminal or Command Prompt: Start by opening your terminal application if you’re on a Linux or Mac operating system. For Windows, open the command prompt.
2. Log in to the PostgreSQL Database: Run the command psql -U username -d databasename. Replace “username” with your PostgreSQL username and “databasename” with the name of the database you want to access. You will be prompted for your password.
3. Show Tables: After you have successfully logged into your PostgreSQL database, use the \dt command to list all the tables in the current database. Simply type \dt and hit enter.

You should now see a list of tables from your PostgreSQL database. It’s worth noting that this command will only display tables from the public schema.

Postgres Command to List All Tables in All Schemas

The command to list all tables in a PostgreSQL database, including those in other schemas, involves a slight modification of the \dt command we used earlier. The revised command is \dt *.*.

Let’s break down the process:

1. Access PostgreSQL: Just like before, start by accessing the PostgreSQL database using psql -U username -d databasename.
2. List All Tables: Once you’re connected to your database, you can list all tables, including those in other schemas, using the command \dt *.*.

Using the *.* parameter tells PostgreSQL to display tables from all schemas. You will now see a complete list of all tables from all schemas within your connected database.

PostgreSQL Show Tables in a Specific Schema

Sometimes, you might want to view only the tables within a specific schema in PostgreSQL. This can be helpful when managing complex databases with multiple schemas. Thankfully, PostgreSQL allows us to do this with a slight modification of the \dt command.

Here’s how you can list tables from a specific schema:

1. Access the PostgreSQL Database: As before, access your PostgreSQL database by running psql -U username -d databasename in your terminal or command prompt.
2. Show Tables in a Specific Schema: To list the tables within a specific schema, you would use the command \dt schemaname.*. Replace “schemaname” with the name of the schema you’re interested in.

For example, if you have a schema named “sales”, you would use the command \dt sales.* to list all tables within that schema.

With these commands, you can effectively manage and navigate your PostgreSQL database. Whether you want to show all tables in the database, list tables across all schemas, or focus on a specific schema, PostgreSQL provides the flexibility to accommodate your needs.