How to Perform the PostgreSQL Show Tables Command in Psql
If you’re new to PostgreSQL a common question is how to simply show a list of the tables in your database. In this article we’ll tackle that task of how to do a PostgreSQL show tables.
Before we proceed on discussing how to perform the show tables command in PostgreSQL we need to make certain prerequisites.
Make sure that there is a PostgreSQL server installed in your local device, execute the terminal command
sudo systemctl status postgresqlthat uses the systemd software suite in a Linux distribution system to view its status.
You can also verify the version of the
psqlshell used for SQL queries in PostgreSQL by executing the command
psql -Vin the terminal.
Accessing the terminal in postgres
PostgreSQL also has an interactive terminal that is used for executing SQL queries or commands for the database.
To access the
psql, execute the bash command :
sudo -u postgres psql
Enter the database in PostgreSQL
To enter the database in PostgreSQL, you must choose on the list of all the names of the database cluster in your local device. You can use the command
\l for that.
The output should look something like the following :
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
dbtest | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
demo | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
djangodb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | orkb=CTc/postg
mydb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
objectrocket | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
orkb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
product | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
And now to connect to a specific database we use the following command :
You are now connected to database "orkb" as user "postgres".
Now here’s the command that you came to the article for. The following command will show all tables in the current database:
The output should look something like the following showing a list of all the tables in your database:
List of relations
Schema | Name | Type | Owner
public | animals | table | postgres
public | demo | table | postgres
public | departments | table | postgres
public | employees | table | postgres
public | payroll | table | postgres
public | some_user | table | postgres
Standard postgresql query
\dt command is the most common way of getting a list of your database tables but it is not the only way. You can also use the following query but as you can see it is far more complex.
SELECT table_schema || '.' || TABLE_NAME AS show_tables
FROM information_schema.tables WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');
The output will give a result like this :
As you can see this also produces a list of your database tables as in the first example.
We hope you have enjoyed this tutorial on how to do show a list of your PostgreSQL tables. We showed you two different ways to use PostgreSQL to show the tables in your database. Thank you for joining us for another ObjectRocket tutorial.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started