intro
Let’s look at the benefits of using show tables PostgreSQL approaches to retrieving a quick overview of tables in a database.
PostgreSQL is a powerful and feature-rich relational database management system. When working with databases, it is essential to clearly understand the available tables. In this article, we will look at the SHOW TABLES PostgreSQL command, which allows you to retrieve a list of tables and gain insights into the database structure. Note that PostgreSQL does not support the SHOW TABLES statement directly like MySQL, but it does provide users with other alternatives.
Here, we will discuss the command's syntax and its implementation. Let’s get right into it!
Show Tables in PostgreSQL: Alternative Methods in PostgreSQL
This section will discuss two methods that PostgreSQL offers users to retrieve a list of tables in a database.
Method #1: Use the PSQL Shell
psql is a terminal-based tool where you can write and execute special command-line instructions. To connect to a particular database, open the terminal on your local computer and run the command below:
1
\c database_name
In this case, assume that the database name is postgres, so here’s how to connect to it:
1
\c postgres
This indicates that you are connected to the specified database. After connecting to a database, list the tables with this command below:
1
\dt
The command will then list all the available tables as shown here:
Using the PSQL to list the available tables
Great! We’ve now been able to list the available tables in our database using the PSQL shell. The next thing is to look at how to show the list of available tables using the pg_catalog schema method.
Method #2: Use the pg_catalog Schema
Another way to show tables in PostgreSQL is to use a SELECT statement to query data from a PostgreSQL system catalog table as below:
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';
The query retrieves the table names from the public schema. Replace public with the desired schema name to retrieve tables from a specific schema. The query filters out any non-base tables, such as views or foreign tables. In this use case, we’re going to employ DbVisualizer:
Using the pg_catalog schema to list all tables.
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.
Conclusion
The SHOW TABLES PostgreSQL command does not exist, sure. 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!
FAQ
Is there a SHOW TABLES command in PostgreSQL?
No, the SHOW TABLES command in PostgreSQL does not exist. It exists in MySQL, but not in PostgreSQL.
Can I see the data within a table using a show tables PostgreSQL approach?
No, the dt command in psql and pg_catalog table 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.
