Exploring the Catalog

Navigate databases, schemas, and tables using psql meta-commands

There's no magic behind PostgreSQL's structure. Databases contain schemas, schemas contain tables, and every piece of that structure is itself stored in special system tables — the catalog — that you can query with ordinary SQL.\n\nWhen a colleague says "the data is in the analytics schema", you don't need to ask for a diagram. You use \l, \dn, \dt analytics.*, and \d tablename to find it in under a minute.\n\nThat's this lab: navigating an unfamiliar database as if you already own it.

<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 480 150" role="img" aria-labelledby="catalog-title">
  <title id="catalog-title">A database contains schemas, which contain tables</title>
  <g fill="none" stroke="#4ade80" stroke-width="2">
    <rect x="10" y="15" width="460" height="125" rx="8"/>
    <rect x="130" y="45" width="325" height="80" rx="8"/>
    <rect x="260" y="75" width="180" height="35" rx="6"/>
  </g>
  <g fill="#4ade80" font-family="sans-serif" font-size="16">
    <text x="25" y="40">Database: beer_db</text>
    <text x="145" y="70">Schema: public</text>
    <text x="275" y="98">Table: breweries</text>
  </g>
</svg>

The Server Hierarchy

PostgreSQL organises data in three levels:

  Server
  └── Databases          ← isolated containers (\l to list)
      └── Schemas        ← namespaces inside a database (\dn to list)
          └── Tables     ← where the data lives (\dt to list, \d to describe)

Databases are fully isolated. A query in beer_db cannot touch a table in social_db without a foreign data wrapper. You connect to one database per session. To switch, run \c other_db or start a new psql session.

Schemas are namespaces inside a database. Multiple schemas let teams share a database without table names colliding. The default schema is called public.

The Meta-Command Cheat Sheet

  \l            → list all databases on the server
  \dn           → list all schemas in the current database
  \dt           → list all tables in the public schema
  \dt schema.*  → list all tables in a specific schema
  \d tablename  → full definition: columns, types, constraints, indexes
  \df           → list all functions

information_schema: Meta-Commands in SQL Form

Every \dt or \d command is secretly a SQL query against information_schema. You can write the same queries yourself — useful in scripts, BI tools, or when you need more than the meta-command gives you:

-- List every table and its schema in the current database
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;

-- Column list for a specific table
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Schema

A named namespace inside a database. Tables, views, and functions belong to a schema. One database can have many schemas — useful for isolating teams or separating application data from reporting data.

information_schema

A standard SQL schema present in every PostgreSQL database. It contains views like tables, columns, constraints, and routines that describe the database's own structure. Querying it is how you inspect metadata in scripts when backslash commands aren't available.

📋 List All Databases

Connect to the server and run \l to see every database the server hosts. Learn to read the output: name, owner, encoding, and collation.

psql -h localhost -U student -d postgres
\l

List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges ------------+------------+----------+-----------------+-------------+-------------+--------+-----------+----------------------- beer_db | beer_db | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | postgres | postgres | UTF8 | libc | C | C.UTF-8 | | | social_db | social_db | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | template0 | postgres | UTF8 | libc | C | C.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | C | C.UTF-8 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres tv_db | tv_db | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | weather_db | weather_db | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | (7 rows)

🚪 Connect to beer_db

Switch to beer_db using the \c command. Watch the prompt change to confirm the switch.

\c beer_db

You are now connected to database "beer_db" as user "student". beer_db=>

🗂️ List the Schemas

Run \dn to see all schemas in this database. You'll find the public schema where all beer_db tables live; PostgreSQL may show pg_database_owner here instead of beer_db because that role represents the current database owner.

\dn

List of schemas Name | Owner --------+------------------- public | pg_database_owner

📦 List Tables in public

Run \dt to list all tables in the public schema. This is the default — no schema prefix needed.

\dt

List of tables Schema | Name | Type | Owner --------+----------------------+-------+--------- public | beer_ratings | table | beer_db public | beers | table | beer_db public | breweries | table | beer_db public | festival_appearances | table | beer_db public | festivals | table | beer_db public | ingredients | table | beer_db public | styles | table | beer_db public | tap_assignments | table | beer_db (8 rows)

🔍 Inspect the breweries Table

Run \d breweries to see the full table definition: columns, data types, constraints, and indexes. This is the equivalent of opening the hood.

\d breweries

Table "public.breweries" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+------------------------ id | uuid | | not null | gen_random_uuid() name | text | | not null | city | text | | not null | country | text | | not null | 'Czech Republic'::text founded_year | integer | | | location | point | | | created_at | timestamp with time zone | | not null | now() Indexes: "breweries_pkey" PRIMARY KEY, btree (id) "breweries_name_key" UNIQUE CONSTRAINT, btree (name) Check constraints: "breweries_founded_year_check" CHECK (founded_year > 1600) Referenced by: TABLE "beers" CONSTRAINT "beers_brewery_id_fkey" FOREIGN KEY (brewery_id) REFERENCES breweries(id) ON DELETE CASCADE TABLE "tap_assignments" CONSTRAINT "tap_assignments_brewery_id_fkey" FOREIGN KEY (brewery_id) REFERENCES breweries(id)

📊 SQL Version: Count Columns per Table

Now do the same thing using only SQL — no backslash shortcuts. Query information_schema.columns to produce a report of every table and its column count in this database.

SELECT table_schema, table_name, COUNT(*) AS column_count FROM information_schema.columns WHERE table_schema NOT IN ('pg_catalog', 'information_schema') GROUP BY table_schema, table_name ORDER BY table_schema, table_name;

table_schema | table_name | column_count le_name ORDER BY table_schema, table_name; --------------+----------------------+-------------- public | beer_ratings | 7 public | beers | 12 public | breweries | 7 public | festival_appearances | 3 public | festivals | 5 public | ingredients | 4 public | styles | 5 public | tap_assignments | 6 (8 rows)

Lab complete! You can navigate any unfamiliar PostgreSQL database from scratch:\n\n\n \l → which databases exist?\n \c dbname → switch to it\n \dn → which schemas?\n \dt schema.*→ which tables?\n \d table → what columns and constraints?\n

Enable JavaScript to run the live terminal and track your progress.