Coming from the MySQL world, it may bring you a powerful query to show a list of tables. You may usually use
SHOW TABLES from MySQL to show a list of available tables.PostgreSQL doesn’t have a
SHOW TABLES query. But it provides you with a tool to list all your tables.In this tutorial, you will learn how to show tables using the PostgreSQL
SELECT query.Why Do You Need to Show Tables?
Well, it really depends on your use case. Just for example, if you revisit an old project or join another team inside your company, you may need to walk through many pieces of data scattered inside the database.
To understand and revive your old memories, you may start by showing all tables and visiting the data.
SELECT Query to Show Tables
To show tables in PostgreSQL, you can leverage the
SELECT query by querying pg_catalog.pg_tables. To filter out tables, you can filter where schemaname != 'pg_catalog' and schemaname != 'information_schema'.You can copy and paste this query into your PostgreSQL query editor.
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
In my database, it results in a list of tables that I use to build a blog.
Conclusion
So that’s it.
It is very easy to show tables in PostgresSQL. All you need to do is fire up your query editor, use the
SELECT query from pg_catalog.pg_tables and apply the required filter to clean up the results.Happy querying!