3 Ways to list tables in a PostgreSQL database
Updated Aug 05th, 2021,Here are a couple of ways to list all tables in a PostgreSQL database. Here we have included 3 possible ways.
1. Using SQL Query
To show the list of all tables in a PostgreSQL database, you can use the following script:
SELECT table_schema , table_name
FROM information_schema.tables
WHERE table_schema not in ('information_schema', 'pg_catalog')
AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name
Sample results
The picture below shows the output:
Or you can use the following script to request the list from PostgreSQL catalog:
SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'information_schema' AND schemaname != 'pg_catalog' ORDER BY tablename
2. Using psql
If you prefer using the psql command line, the following command would help \dt and for more details, you can use \dt+.
3. You could also get this with ERBuilder Data Modeler
Finally, we will have a look at ERBuilder Data Modeler which can extract data structure from your database. From there you can visualize all the tables as a list (shown in red in the figure below) or as an ER Diagram (shown in blue) as you can explore and browse any table you want easily with the data model browser module (shown in green).
Furthermore, with the use of ERBuilder Data Modeler you can also view details of the table and edit it, simply by double-clicking on the table name. As you can see from the picture below, you get the table's columns, indexes, constraints, keys, triggers, description, and the SQL script.
How ERBuilder Helps
ERBuilder Data Modeler is a GUI data modeling tool that allows you to visualize, design and model databases by using entity relationship diagrams and automatically generates the most popular SQL databases including Amazon Redshift. Generate and share the data Model documentation with your team. Optimize your data model by using advanced features such as test data generation, schema compare and schema synchronization. Try now ERBuilder for 15 days FREE
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
