Use Psql to List Tables in PostgreSQL
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.
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
systemdin Linux. Just type the command
sudo systemctl status postgresqlto see if PostgreSQL is actively running on your machine.
To find out which version of PostgreSQL is installed, just use the command
psql -vin the terminal.
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:
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:
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:
When you execute this command, your results will look like the following:
List OF relations
Schema | Name | TYPE | Owner
public | demo | TABLE | postgres
public | employee | TABLE | postgres
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
Let’s look at an example of this query in action:
SELECT * FROM
schemaname != 'pg_catalog'
schemaname != 'information_schema'
NOTE: Notice that we omit tables that come from
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:
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
public | demo | postgres | | t | f | f | f
public | employee | postgres | | t | f | f | f
As you can see, the query was successful, returning the
employee tables that exist within the current database.
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