How to Perform the PostgreSQL Show Tables Command in Psql

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

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.

Prerequisites

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 postgresql that uses the systemd software suite in a Linux distribution system to view its status.

  • You can also verify the version of the psql shell used for SQL queries in PostgreSQL by executing the command psql -V in the terminal.

Screenshot of the psql status

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 :

1
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.

1
\l

The output should look something like the following :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
                                   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
res
 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 :

1
2
\c orkb;
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:

1
\dt

The output should look something like the following showing a list of all the tables in your database:

1
2
3
4
5
6
7
8
9
            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

The \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.

1
2
3
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 :

1
2
3
4
5
6
7
8
    show_tables
--------------------
 public.payroll
 public.employees
 public.departments
 public.animals
 public.demo
 public.some_user

As you can see this also produces a list of your database tables as in the first example.

Conclusion

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.