How To List Databases and Tables in PostgreSQL

Sometimes you may need to view all databases and tables in PostgreSQL. PostgreSQL offers handy meta commands that allow you to quickly list all databases and tables that you have access to. These meta commands are automatically translated into SQL queries and run by PostgreSQL server. They start with a backslash and are very handy for database administrators to quickly get required information.


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.

You will see an output similar to the one below.

postgres=# \l
                                         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


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 sales database to postgres database.

sales=# \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
 public | meeting        | table | postgres
 public | monthly_sales  | table | postgres
 public | order_status   | table | postgres
 public | orders         | table | postgres
 public | product_sales  | table | postgres
 public | product_sales1 | table | postgres
 public | sales          | table | postgres
 public | sales2         | table | postgres
 public | sales_data     | table | postgres

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.

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!