Suppose you have to list all the tables of PostgreSQL database. You can achieve this by various means.

Using psql commands

The psql command to list all the tables of the currently selected database is \dt (dt as “display tables”). Read on if you need to know a little bit more details…

First, connect to the database using the psql CLI. If you want to check all the databases and their schemas login as the postgres superuser:

$ psql -h <IP adress of DB server> -U postgres -p <DB server port> -d <database>

Use 127.0.0.1 if the DB is running on your local machine. 5432 is the default port. And postgres is the default DB (use the template1 database if you have problems).

$ psql -h 127.0.0.1 -U postgres -p 5432 -d postgres

Then, if you need to list all the databases (remember l as in “list”):

\l

Next select the database (remember c as in “change”):

\c <database name>

Finally, to list all tables in the current schema use this command (remember dt as in “display tables”):

\dt

Keep in mind that MySQL only has one database but multiple schemas, whereas Postgres can have mutliple databases (catalogs) and schemas.

Using SQL

The following SQL statement lists all system and the user-created tables (in the current database):

SELECT * FROM pg_catalog.pg_tables;

If you want to omit any system tables use the following SQL statement:

SELECT * FROM pg_catalog.pg_tables 
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

It is also possible to list all the databases (regardless of the database you have currently selected):

SELECT * FROM pg_database;