How To List Databases and Tables in PostgreSQL

Last updated on August 22nd, 2024 at 05:13 am

Often database administrators need to view all databases and tables in PostgreSQL. They need to find out which databases are present on the server, or if a specific database is present on it. Sometimes, they also need to find out which tables are present in a database. PostgreSQL offers several simple ways to solve this problem. It comes with handy meta commands that allow you to quickly list all databases and tables that you have access to. Alternatively, you can also use SQL queries to get this information. In this article, we will learn how to list databases & tables in PostgreSQL.

What Are Meta Commands

Meta Commands are shortcut commands that you can enter in psql client. It is parsed by psql and automatically translated into appropriate SQL query that can be fired to one or of system tables in your database. They start with a backslash, followed by arguments. Meta commands are a big time saver, especially for administrators.

How To List Databases and Tables in PostgreSQL

Here are a couple of simple PostgreSQL meta commands to list all databases and tables in PostgreSQL.

List Databases in PostgreSQL

Log into psql command line PostgreSQL tool and run the \list or \l command to list all databases that you have access to. Since a PostgreSQL server can contain multiple databases, and each database is stored as a file, it will give you a list of all

You will see an output similar to the one below.

postgres=# \l

OR

postgres=# \list
                                         List of databases
   Name    |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-----------+----------+----------+--------------------+--------------------+-----------------------
 postgres  | postgres | UTF8     | English_India.1252 | English_India.1252 |
 sample_db | postgres | UTF8     | English_India.1252 | English_India.1252 |
 template0 | postgres | UTF8     | English_India.1252 | English_India.1252 | =c/postgres          +
           |          |          |                    |                    | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_India.1252 | English_India.1252 | =c/postgres          +
           |          |          |                    |                    | postgres=CTc/postgres

Also read : How to Create Read Only User in PostgreSQL

Alternatively, you can also use SQL query to get a list of databases. The datname column system table pg_database contains names of all databases in your server. Here is a SQL query to get the list of databases.

SELECT datname FROM pg_database;

Of course, you can also use visual tools like pgAdmin for this purpose.

Switch Databases

In order to list tables in PostgreSQL, you need to first switch to the specific database whose tables you want to view.

You can switch databases using \c command followed by database name. Here’s an example to switch from test database to postgres database.

test=# \c postgres
You are now connected to database "postgres" as user "ubuntu". 
postgres=#

Also read : How to Compare Two Schemas in PostgreSQL

List tables in PostgreSQL

Please note, you can list all tables only after you switch to a database. You can list all tables in a database using \dt meta command.

You will see an output similar to the one below.

postgres=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | exams          | table | postgres
 public | login          | table | postgres
 ...

Conclusion

In this article, we have learnt several easy ways to quickly get a list of databases and files in PostgreSQL. You can use meta commands or SQL queries in psql client for this purpose. Alternatively, you can also use GUI clients like pgAdmin for the same. Hopefully, the above article will help you list databases and tables in PostgreSQL.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.