2 Ways to Find Your Tables in PostgreSQL: A Complete Guide

Getting started with a new database system can feel like exploring a new city. You need a map to find your way around and locate key landmarks.

In PostgreSQL, one of the first “landmarks” you’ll want to find is your list of tables.

If you have a background in MySQL, you might instinctively type SHOW TABLES. While that’s a handy command, PostgreSQL has its own powerful and flexible ways to get the job done. This guide will walk you through several simple methods to list tables, making you feel right at home with Postgres.

Let’s dive in!

Table of Contents

First, A Quick Word on Schemas

Before we find our tables, it’s helpful to understand how PostgreSQL organizes them. It uses something called schemas. Think of a schema as a folder or a namespace inside your database. It holds a collection of objects like tables, views, and functions, allowing you to group them logically.

By default, every new database has a public schema, which is where your tables are created unless you specify otherwise. Using schemas is a fantastic way to keep a large database organized, manage user permissions, and even have tables with the same name in different schemas without any conflict.

Now, let’s see how to list tables, keeping schemas in mind.

The Quick & Easy Way: Using the `psql` Command-Line Tool

The most direct method is to use psql, PostgreSQL’s own interactive terminal. It’s a powerful tool with some handy built-in commands that make database administration a breeze.

Step 1: Connect to Your Database

First, open your computer’s terminal or command prompt and connect to your PostgreSQL server. A common way to do this is by specifying the user.

psql -U postgres

This command connects you to the PostgreSQL server as the default postgres user. From there, you can switch to a specific database, like dvdrental, using the \c command.

\c dvdrental

As a handy shortcut, you can also connect directly to your desired database when you first log in by using the -d flag.

psql -U postgres -d dvdrental

Step 2: List the Tables

Now for the main event! With your database selected, simply type the \dt command and hit enter.

\dt

This command will display a neatly organized list of all the tables within the public schema.

             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 ...
(15 rows)

Want more details? You can get an expanded view that includes table size and a description by adding a plus sign (+) to the command.

\dt+

This gives you a richer output, which is great for getting a quick overview of your database’s structure and size.

                                         List of relations
 Schema |     Name      | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+---------------+-------+----------+-------------+---------------+------------+-------------
 public | actor         | table | postgres | permanent   | heap          | 40 kB      |
 public | address       | table | postgres | permanent   | heap          | 88 kB      |
 ...
(15 rows)

Advanced psql Tricks for Listing Tables

The \dt command is more versatile than it first appears. You can add patterns to filter your results in powerful ways.

List tables from a specific schema: If you want to see tables only from a certain schema, you can specify it before the wildcard.
```
\dt your_schema_name.*
```
Filter tables by name: Looking for a table but only remember part of its name? Use wildcards like (matches any sequence of characters) and ? (matches any single character).
```
-- Find all tables starting with 'film'
\dt film*

-- Find all tables ending with 'or'
\dt *or
```
List tables across all schemas: To see every table in your database, regardless of its schema, use a double wildcard.
```
\dt *.*
```

This command-line approach is fast, efficient, and perfect for everyday use. But what if you want to retrieve this information using a standard SQL query?

Peeking Under the Hood: Querying the System Catalogs

For a more universal method that works in any SQL client, not just psql, you can directly query PostgreSQL’s system catalogs.

Every PostgreSQL database has a special schema called pg_catalog. Think of it as the database’s own internal reference guide, where it stores all the metadata about your tables, columns, and other objects.

By querying a view within this schema, you can get a list of your tables.

The Basic Query

Here’s the SQL query to do it:

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
      schemaname != 'information_schema';

Let’s quickly break that down:

SELECT * FROM pg_catalog.pg_tables: This tells PostgreSQL that we want to see all the information from the pg_tables view, which lives inside the pg_catalog schema.
WHERE schemaname != 'pg_catalog' AND ...: This part is important. It filters out the system’s own internal tables. Without this, you’d see a long list of tables that PostgreSQL uses to manage itself, which can be overwhelming.

Advanced SQL Queries for Listing Tables

Just like with psql, you can make your SQL queries more specific.

Filter by a specific schema: To see tables from just one schema, add it to the WHERE clause.
```
SELECT tablename, tableowner
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';
```
Filter by table name using LIKE: The LIKE operator lets you use wildcards in your query. The percent sign (%) matches any sequence of characters, and the underscore (_) matches a single character.
```
-- Find all tables whose names contain 'film'
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public' AND tablename LIKE '%film%';
```
Find tables owned by a specific user: You can easily check which tables belong to a particular user by filtering on the tableowner column.
```
SELECT tablename, schemaname
FROM pg_catalog.pg_tables
WHERE tableowner = 'specific_username';
```

Wrapping Up

While PostgreSQL doesn’t have a SHOW TABLES command, it offers powerful and straightforward alternatives that give you even more control.

For a quick look inside your database from the command line, the \\dt command in psql is your best friend.
For a more flexible, query-based approach that works in any tool, you can look inside the pg_catalog.pg_tables view.

Now you have a complete toolkit for finding your tables and navigating your PostgreSQL databases with confidence. Happy querying