Having recently transitioned from MySQL to PostgreSQL, I've been facing some challenges with the syntax and command differences. In MySQL, we often use the 'SHOW TABLES' command to list all tables in the currently selected database. This would give us a clear overview of the tables at our disposal. However, PostgreSQL doesn't seem to recognize this command. I am looking for the PostgreSQL equivalent of this function. The point is to list all the tables within a specific database, preferably without having to dive into additional tools like pgAdmin. Any insights into achieving this would be greatly appreciated.
I've tried looking into the documentation but haven't found something as straightforward as the MYSQL 'SHOW TABLES'. Could anyone provide the correct SQL command to retrieve this information?

Certainly, PostgreSQL handles things slightly differently. You need to query the system catalog or use some helper functions provided by PostgreSQL. You can achieve the equivalent result by using a SELECT statement on the pg_tables system catalog. Here's an example of how you can do this:


This will list all tables in the current database that are not system tables.

Another approach to retrieve the list of all tables is using the information_schema.tables view, which is an SQL-standard way of querying meta-data and can be portable across different databases:


This will list all the tables in the 'public' schema, which by default contains the user-created tables.

If you are specifically looking for a command that is somewhat close to the MySQL 'SHOW TABLES', then you may find the '\dt' command in the psql command-line interface useful. It's not SQL, but a built-in psql command-line tool command that lists the tables:
In the psql interface you can simply type:


Keep in mind that this will only work in the psql CLI and not as part of a SQL query you can run in, say, a script or a program.