Use Psql to List Tables in PostgreSQL
Introduction
When you’re using PostgreSQL to store data in tables, there may be times when you need to obtain a list of all the tables in a given database. In some cases, you may only want a list of the tables you created, omitting certain system tables created by PostgreSQL. Fortunately, it’s easy to access the exact table information you need using the psql
command-line interface for PostgreSQL. In this article, we’ll show you how to connect to your database and use psql to list tables in PostgreSQL.
Prerequisites
Before we go any further with our tutorial, let’s look at a few prerequisites that are essential for this task:
You’ll need to have a PostgreSQL database cluster installed on your local device. If you’re not sure whether it’s installed, you can verify by using
systemd
in Linux. Just type the commandsudo systemctl status postgresql
to see if PostgreSQL is actively running on your machine.To find out which version of PostgreSQL is installed, just use the command
psql -v
in the terminal.
Access psql
In order to execute queries and other commands in the PostgreSQL database, you’ll need to access the psql console. It’s in this command-line interface where you’ll be able to input SQL commands and list the tables in a certain database.
To enter and access the psql console, use the following command:
1 | sudo su - postgres |
After entering the password, type the command psql
to access the database.
PostgreSQL list tables
Once you’re in the psql console, all you need to do to list tables in a given database is to use the command \dt
inside the current database. Of course, it’s important to connect to that database first.
Connect to a PostgreSQL database
Using the psql console, enter the command \l
to display a list of all the database in your PostgreSQL environment. You should see output that looks like this:
1 2 3 4 5 6 7 8 9 | 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 | objectrocket | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | sample | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | somedb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | |
Next, enter the command \c
to connect to a specific database for which you’d like to list the tables.
The basic syntax used to list tables in PostgreSQL database is:
1 | \dt |
When you execute this command, your results will look like the following:
1 2 3 4 5 6 | List OF relations Schema | Name | TYPE | Owner --------+----------+-------+---------- public | demo | TABLE | postgres public | employee | TABLE | postgres (2 ROWS) |
Using the pg_catalog schema
Another way is to list tables in PostgreSQL is by using the SELECT
statement to query the data of the pg_catalog
schema:
Let’s look at an example of this query in action:
1 2 3 4 5 6 | SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' |
NOTE: Notice that we omit tables that come from pg_catalog
and information_schema
; this ensures that your query only returns tables that you have created.
Using this query, we’ll get results that look like the following:
1 2 3 4 5 | schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+-----------+------------+------------+------------+----------+-------------+------------- public | demo | postgres | | t | f | f | f public | employee | postgres | | t | f | f | f (2 ROWS) |
As you can see, the query was successful, returning the demo
and employee
tables that exist within the current database.
Conclusion
Being able to list tables in PostgreSQL is an important component of good database administration. Whether you want to list all tables in a given database or just the ones that you created, it’s easy to get the appropriate information using the psql command-line interface. In this article, we looked at a few different ways you can use psql to list tables in PostgreSQL. Using the methods described in this tutorial, you’ll have no problem obtaining a list of tables in your own PostgreSQL environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started