The Layered World of Postgres Objects

Postgres—and Redshift, if you lean that way—organizes its objects in a nested hierarchy. At the top sits the cluster, the full installation. Users and groups live at this level and are shared across every database within the cluster. Beneath the cluster, each database groups a set of schemas, and each schema in turn groups a set of relations—tables, views, indexes, functions, and any other named object.

Naming rules follow the container boundaries: two relations can share a name as long as they live in different schemas, but duplicates within a single schema are rejected. Navigating unfamiliar arrangements with these layers and possible name collisions can be fiddly with standard SQL tools, but psql offers a handful of shortcuts designed for exactly this.

Moving Between Databases

To see every database in the cluster, use \list (or its short form \l):

\l

Switching to another database is done with \connect (\c):

\c postgres

Inspecting Schemas and Relations

Within a database, \dn lists the schemas:

\dn

To list the relations, run \d:

\d

Psql lets you narrow the output by relation type with the suffixes \d{E,i,m,s,t,v}—covering tables, indexes, views, and more. These are commonly paired with a pattern pointing to a schema. For example, this shows all tables and views inside my_schema:

\dtv my_schema.

To drill into a single relation, describe it with \d; use \d+ for additional detail:

\d my_schema.my_table

A Built-In Cheat Sheet

The growing set of backslash commands is easy to forget. Instead of memorizing them, pull up \?, which prints psql’s own quick reference for every backslash command:

\?

If you take away one command from this, make it \?.

Respecting the search_path

One more piece of the puzzle is the search_path setting, which shapes how psql resolves and displays objects:

  1. When a symbol lacks a schema prefix, Postgres tries each schema in search_path in order. Schemas outside it are ignored, so an unprefixed object living there will not resolve.
  2. When you run \d without a search pattern, only schemas on search_path are shown.
  3. It can subtly change behavior elsewhere—for instance, in Redshift, pg_table_def reports only tables in schemas that appear on the path.

You can set the path explicitly, and the setting works in ~/.psqlrc too:

set search_path to '$user', infra, logs, public;