List All Tables In Database Postgres

When working with PostgreSQL, one of the most fundamental tasks for developers, database administrators, and data analysts is the ability to list all tables in a database. Whether you are debugging a migration script, exploring an unfamiliar schema, or writing dynamic SQL, knowing how to retrieve a comprehensive inventory of relations is essential. Still, postgreSQL offers multiple approaches to achieve this, ranging from simple meta-commands in the psql terminal to querying the system catalogs directly via standard SQL. Understanding the nuances of each method—specifically regarding schema visibility, table types, and permission contexts—ensures you retrieve exactly the data you need without noise Simple, but easy to overlook. Still holds up..

Most guides skip this. Don't.

Using the psql Meta-Commands

For anyone spending time inside the PostgreSQL interactive terminal (psql), the backslash commands are the fastest way to inspect database objects. These are not standard SQL; they are client-side shortcuts parsed by psql itself That's the part that actually makes a difference. Simple as that..

The Standard \dt Command

The most common command is \dt. By default, this lists all tables (excluding views, sequences, and foreign tables) in the schemas present in your current search_path, typically the public schema.

\dt

The output provides a clean, formatted table showing the Schema, Name, Type (always "table" here), and Owner.

Listing Tables Across All Schemas

A default \dt often hides tables residing in schemas outside your search path (such as pg_catalog, information_schema, or custom application schemas). To see everything, append the * wildcard:

\dt *.*

This pattern tells psql to match any schema (*) and any table name (*). It is the equivalent of "select all tables in database postgres" regardless of namespace.

Including System Catalogs and Hidden Objects

PostgreSQL system catalogs are technically tables, but they are usually hidden from casual inspection. To include them, add the S (system) modifier:

\dtS
\dtS *.*

Getting Extended Information

If you need more metadata—such as table size, persistence (permanent, temporary, unlogged), or access method (heap)—use the + modifier:

\dt+
\dt+ *.*

This extended view is invaluable for capacity planning and identifying unlogged tables which are not crash-safe That's the whole idea..

Querying the information_schema

The information_schema is an ANSI SQL standard feature implemented by PostgreSQL. Even so, it provides a portable, read-only view of the database metadata. Because it adheres to a standard, queries written against it are more likely to work across different database systems (like MySQL or SQL Server) with minimal changes Simple, but easy to overlook..

The Core tables View

The primary view for this task is information_schema.tables. A basic query to list user-defined tables looks like this:

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Key columns explained:

• table_schema: The namespace the table belongs to.
• table_name: The identifier of the relation.
• table_type: Distinguishes BASE TABLE (standard tables), VIEW, FOREIGN TABLE, and LOCAL TEMPORARY.

Filtering by Schema

Often, you only care about a specific schema, such as public or a tenant-specific namespace:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE';

Advantages and Limitations

The main advantage of information_schema is portability and stability. The PostgreSQL project guarantees backward compatibility for these views. On the flip side, it can be slower than querying system catalogs directly because it involves complex joins across multiple internal views to enforce standard compliance and security checks (like hiding tables the current user has no privileges on).

This is where a lot of people lose the thread.

Querying the pg_catalog System Catalogs

For performance-critical scripts, administrative tooling, or when you need metadata not exposed by the standard (like OIDs, tablespace names, or toast table links), querying pg_catalog directly is the "PostgreSQL native" way. The primary catalog for relations is pg_class Took long enough..

Understanding pg_class

pg_class contains every relation: tables, indexes, sequences, views, materialized views, composite types, and toast tables. To filter strictly for standard tables, you must check the relkind column Most people skip this — try not to. That's the whole idea..

• r = Ordinary table
• p = Partitioned table
• t = TOAST table (usually hidden)
• v = View
• m = Materialized view
• i = Index
• S = Sequence
• f = Foreign table

The Standard Native Query

To replicate the output of \dt *.* (user tables across all schemas), you must join pg_class with pg_namespace (which stores schema information):

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       CASE c.relkind
           WHEN 'r' THEN 'table'
           WHEN 'p' THEN 'partitioned table'
           WHEN 'f' THEN 'foreign table'
           ELSE 'other'
       END AS table_type,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'f')  -- Tables, partitioned tables, foreign tables
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_catalog.pg_table_is_visible(c.oid) -- Respects search_path visibility
ORDER BY schema_name, table_name;

The Role of pg_table_is_visible

Notice the pg_table_is_visible(c.Still, oid) function in the WHERE clause. This is a critical security and usability feature. It returns true only if the table is in a schema listed in the current user's search_path and the user has USAGE privilege on that schema. Which means this mimics the behavior of \dt without the *. And * wildcard. If you want to see absolutely everything the user has permission to see (even if not in search_path), remove that function call and rely solely on the namespace filter Which is the point..

Accessing Rich Metadata

Because you are querying the source catalog, you have immediate access to low-level details without extra joins:

• c.reltuples: Estimated row count (from last ANALYZE).
• c.relpages: Disk pages consumed.
• c.relpersistence: p (permanent), u (unlogged), t (temporary).
• c.reloptions: Storage parameters (fillfactor, autovacuum settings).

Programmatic Access: JDBC, Python, and ORMs

Application developers rarely type SQL manually to list tables; they use drivers and ORMs.

JDBC DatabaseMetaData

In Java applications, the standard API is java.sql.DatabaseMetaData.getTables():

DatabaseMetaData meta = connection.getMetaData();
ResultSet rs = meta.getTables(null, null, "%", new String[]{"TABLE"});
while (rs.next()) {
    String schema = rs.getString("TABLE_SCHEM");
    String name = rs.getString("TABLE_NAME");
    System.out.println(schema + "." + name);
}

The arguments are Catalog, Schema Pattern, Table Name Pattern, and Types. Passing null for schema acts as a wildcard It's one of those things that adds up..

Python psycopg2 / psycopg3

In Python

Python psycopg2 / psycopg3

Both the legacy psycopg2 and the newer psycopg3 drivers expose PostgreSQL’s system catalogs through ordinary SQL, so you can reuse the query from the previous section or rely on the driver’s introspection helpers.

Using raw SQL with psycopg2

import psycopg2

conn = psycopg2.relowner) AS owner
    FROM pg_catalog.{name} ({ttype}) – owner: {owner}")
cur.connect(dsn="dbname=mydb user=me")
cur = conn.relkind
               WHEN 'r' THEN 'table'
               WHEN 'p' THEN 'partitioned table'
               WHEN 'f' THEN 'foreign table'
               ELSE 'other'
           END AS table_type,
           pg_get_userbyid(c.nspname NOT IN ('pg_catalog', 'information_schema')
      AND pg_catalog.pg_class c
    JOIN pg_catalog.fetchall():
    print(f"{schema}.nspname AS schema_name,
           c.pg_table_is_visible(c.relname AS table_name,
           CASE c.pg_namespace n ON n.relnamespace
    WHERE c.oid = c.relkind IN ('r', 'p', 'f')
      AND n.Plus, oid)
    ORDER BY schema_name, table_name;
""")
for schema, name, ttype, owner in cur. On top of that, execute("""
    SELECT n. cursor()
cur.close()
conn.

#### Using raw SQL with `psycopg3`

```python
import psycopg

with psycopg.connect("dbname=mydb user=me") as conn:
    with conn.In real terms, cursor() as cur:
        cur. execute("""
            SELECT n.nspname, c.relname,
                   CASE c.relkind
                       WHEN 'r' THEN 'table'
                       WHEN 'p' THEN 'partitioned table'
                       WHEN 'f' THEN 'foreign table'
                       ELSE 'other'
                   END
            FROM pg_catalog.pg_class c
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.Consider this: relkind IN ('r', 'p', 'f')
              AND n. nspname NOT IN ('pg_catalog', 'information_schema')
              AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1, 2;
        """)
        for schema, name, ttype in cur:
            print(f"{schema}.

Both snippets produce the same list you would see with `\dt *.So *`, but they give you the freedom to add extra columns (e. Practically speaking, g. , `c.reltuples`, `c.reloptions`) without changing the driver code.

### ORM‑level introspection

When an ORM is in the picture, you usually let the framework handle the catalog lookup. Below are the most common ways to obtain a list of tables in the major Python‑ecosystem ORMs and their Java counterparts.

#### SQLAlchemy (Core & ORM)

```python
from sqlalchemy import create_engine, inspect

engine = create_engine("postgresql+psycopg2://me@localhost/mydb")
inspector = inspect(engine)

for schema_name in inspector.get_schema_names():
    if schema_name in {"pg_catalog", "information_schema"}:
        continue
    for table_name in inspector.get_table_names(schema=schema_name):
        print(f"{schema_name}.

SQLAlchemy’s `Inspector` internally runs queries similar to the one shown earlier, but it also caches results and provides convenient helpers for columns, indexes, foreign keys, etc.

#### Django ORM

Django does not expose a public API for “list all tables in the database” because it assumes you work with the models defined in your project. Even so, you can still introspect the connection:

```python
from django.db import connection

with connection.So cursor() as cursor:
    cursor. relkind = 'r'
          AND n.relnamespace
        WHERE c.oid)
    """)
    for schema, table in cursor.execute("""
        SELECT n.That's why pg_class c
        JOIN pg_catalog. nspname, c.pg_table_is_visible(c.pg_namespace n ON n.Day to day, nspname NOT IN ('pg_catalog', 'information_schema')
          AND pg_catalog. Plus, relname
        FROM pg_catalog. oid = c.fetchall():
        print(f"{schema}.

If you need Django‑model metadata, you can iterate over `apps.Still, get_models()` and read each model’s `_meta. db_table`.

#### Hibernate (Java)

In Hibernate you can obtain table names via the `Metadata` object:

```java
Metadata metadata = sessionFactory.getMetadata();
for (EntityType entity : metadata.getEntityBindings()) {
    String tableName