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