Last updated at 2023-09-03

SELECT Query to Show Tables in PostgreSQL

Last Edit By
Last edited time
Sep 3, 2023 06:08 AM
AI Status
Aug 31, 2023
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.


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!

Discussion (0)

Related Posts