How to See All Tables in PostgreSQL Database

Overview

Discover how to list all your tables in a PostgreSQL database, a fundamental task for database management and queries.

Introduction

Working with databases often requires knowledge about the structure and organization of the data. If you’re dealing with PostgreSQL, one of the most useful pieces of information is knowing how to see all the tables within a given database. This is essential for debugging, data analysis, and managing your database schema. In this tutorial, we will explore how to view complete table listings using PostgreSQL’s interactive terminal, as well as through other interfaces like pgAdmin and SQL queries.

Using psql Command Line Tool

The psql tool is a powerful command-line interface that allows you to interact with your PostgreSQL database in a variety of ways. To get started, open your terminal and connect to your PostgreSQL database using the following command:

psql -U username -d databasename

Once connected, you can list all tables within the current database using the following query:

\dt

If you want to view tables from all schemas, you can use:

\dt *.*

Information Schema Query

You can also use a SQL query to retrieve table information. The information_schema.tables view is an ANSI-standard view that contains all the table metadata. To see all tables in your current database, you can run:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog');

This will display only the tables created by the user and exclude system tables.

Using the pgAdmin GUI

If you prefer a graphical interface, pgAdmin provides an intuitive way to view tables. After connecting to your desired database, simply navigate through the Object Browser’s tree structure to the ‘Tables’ node to see a list of all available tables.

Advanced Operations

PostgreSQL also allows more intricate queries to get extended information about tables, like table sizes, indexes, ownership, and more. Here, we’ll retrieve the approximate table size along with the table name:

SELECT table_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables 
WHERE table_schema = 'public';

This gives an overview of database sizes which can be essential for performance tuning and capacity planning. Make sure to replace ‘public’ with the schema name you wish to check if you’re using a different schema.

Filter Tables by Name Pattern

When searching through a massive number of tables, it might be helpful to filter them based on a name pattern. The following SQL query provides this functionality:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%pattern%'
AND table_schema NOT IN ('information_schema', 'pg_catalog');

Replace ‘%pattern%’ with the desired text you wish to match in the table names.

Listing Table Ownership

Similar to viewing table sizes, you can check which user owns which table using the following query:

SELECT table_schema, table_name, tableowner
FROM pg_tables;

Note that this query lists all tables, including the system tables.

Conclusion

Viewing all tables in a PostgreSQL database is an essential skill for effective database administration. Whether using the command line or a graphical user interface, understanding how to access, filter, and gather metadata about your tables can greatly streamline your workflow. Armed with the commands and queries outlined in this tutorial, you can navigate your PostgreSQL databases with confidence.