Browse PostgreSQL tables

Listing and inspecting PostgreSQL tables.

Opening an unfamiliar database means answering three questions in order: what tables exist, what is in this one, and where is the column I am looking for. Each has a one-line answer.

Short answer: In psql, \dt lists tables in your search path, \d tablename shows a table's columns, indexes, and foreign keys, and \dn lists schemas. If \dt shows nothing, your tables are in a schema outside the search path — \dt *.* lists everything. A GUI client shows the same information as a tree you can click.

Download PgDeck View pricing

PgDeck PostgreSQL desktop client with schema navigation, table filtering, row editing, and SQL tools

Why developers use PgDeck

PgDeck is shaped around PostgreSQL tasks that happen every day: connect, inspect, filter, edit, query, manage structure, copy, and export.

\dt only shows the search path; \dt *.* shows everything

\d tablename is the fastest way to understand one table

information_schema.columns finds a column across every table

pg_total_relation_size includes indexes and TOAST, unlike row counts

Foreign keys are how you find the real shape of a schema

Secure connections

Save PostgreSQL connection details with encrypted passwords, SSL settings, URL import, testing, search, edit, and delete actions.

Schema and data grid

Browse schemas and tables, search the table list, pin favorites, filter rows, sort columns, resize columns, and paginate results.

SQL workspace

Run SELECT and non-SELECT statements from multi-tab SQL editors with syntax highlighting and interactive query results.

Staged editing

Edit cells, add rows, duplicate rows, set NULL, stage deletes, review pending changes, and commit everything in one transaction.

How to browse PostgreSQL tables

1
List what exists
\l lists databases, \dn lists schemas, and \dt lists tables in the current search path. That last qualifier is why \dt sometimes returns nothing on a database that clearly has tables — they are in a schema you are not looking at.
```
\l          -- databases
\dn         -- schemas
\dt         -- tables in the search path
\dt *.*     -- tables in every schema
\dt sales.* -- tables in one schema
```
2
Inspect one table
\d tablename shows columns, types, nullability, and defaults, plus indexes, constraints, and foreign keys. \d+ adds storage details, column comments, and the table size. This is the fastest way to understand a table you have never seen.
```
\d users
\d+ users
```
3
Query the catalog instead
Anything psql shows you is a query underneath, and writing it yourself means you can filter and sort. information_schema is the SQL-standard view and works across databases; pg_catalog is PostgreSQL-specific and faster. Use information_schema unless you need something only pg_catalog exposes.
```
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;
```
4
Find a column when you do not know the table
Looking for where customer_id lives, or every table with an email column, is a catalog query rather than guesswork. This is one of the most useful queries to keep to hand when working in a schema someone else designed.
```
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name ILIKE '%email%'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;
```
5
See which tables are actually large
Row counts mislead because a table with wide rows or heavy bloat takes far more space than its count suggests. pg_total_relation_size includes indexes and TOAST data, which is what actually occupies disk.
```
SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
```
Or browse in a GUI client
Exploration is what a schema tree is genuinely better at than a terminal. PgDeck lists schemas and tables with search and pinned favourites, shows a column inspector with types, defaults, nullability and indexes, and generates an ERD from your foreign keys so the relationships are visible rather than inferred.

search_path explains most confusion

PostgreSQL resolves unqualified table names against the search path, which usually contains just public. If an application creates its tables in a different schema, \dt shows nothing and an unqualified SELECT fails with "relation does not exist" even though the table is right there. \dn lists schemas and \dt *.* lists tables regardless of path.

information_schema or pg_catalog

information_schema is the SQL standard, portable across database systems, and easier to read. pg_catalog is PostgreSQL's own and exposes everything, including things the standard has no concept of — table bloat, index usage statistics, TOAST. Start with information_schema and drop to pg_catalog when you need what it cannot tell you.

Follow the foreign keys

The fastest way to understand an unfamiliar schema is not to read every table, it is to read the foreign keys and see how they connect. \d tablename lists them per table, and a client that draws an ERD from them gives you the whole shape at once. A schema with no foreign keys at all is itself a useful finding.

Views and materialised views hide in plain sight

\dt lists only base tables. \dv lists views, \dm lists materialised views, and querying something that turns out to be a view explains why a column cannot be updated or why a query is slower than the table size suggests. When something behaves oddly, check what kind of relation it actually is.

Reading a table is not free on a busy database

SELECT * with no LIMIT on a large table pulls every row across the network and can sit in a transaction longer than you expect. When exploring production, add a LIMIT by reflex. GUI clients paginate for exactly this reason.

How do I list all tables in PostgreSQL?

In psql, \dt lists tables in the current search path and \dt *.* lists them in every schema. In SQL, query information_schema.tables filtering out pg_catalog and information_schema.

Why does \dt show no tables?

Because \dt only shows the current search path, which is usually just public. If the application created its tables in another schema, they will not appear. Run \dn to list schemas and \dt *.* to list tables across all of them.

How do I see a table's columns and indexes?

\d tablename shows columns, types, nullability, defaults, indexes, constraints, and foreign keys. \d+ tablename adds storage details, comments, and the table size.

How do I find which table contains a column?

Query information_schema.columns filtering on column_name, excluding the pg_catalog and information_schema schemas. This is much faster than inspecting tables one at a time in an unfamiliar database.

How do I find the largest tables in a database?

Order by pg_total_relation_size, which includes indexes and TOAST storage rather than just the table's own rows. Row counts are misleading because a table with wide rows or bloat can occupy far more disk than its count suggests.