Exploring Object Hierarchies in Psql

Postgres (or Redshift if you’re so inclined) arranges itself through a set of nested objects with familiar constructs like a table or an index stored in the highest logical layer. The complete hierarchy looks like this:

• Cluster: The top-level Postgres installation. Users and groups are shared between all databases in a cluster, but other objects are contained within a database.
  • Database: Groups together a set of schemas. May have a default schema so that relations can be created within it directly.
    • Schema: Groups together a set of relations. Duplicate relation names are not allowed in the same schema, but are allowed in different schemas.
      • Relation: Any other type of named Postgres object like a table, view, index, or function.

See the Postgres documentation for more information about each of these layers.

Between complex nesting and name collisions, using Postgres tools to navigate unfamiliar arrangements of these objects can be a little tricky. Luckily psql exposes some handy shortcuts for working with them.

Databases

We can use \list (\l) to list all the databases in a cluster:

\l

A different database in the cluster can be accessed with \connect (\c):

\c postgres

Schemas

We can use \dn to list schemas within a database:

\dn

Relations

Relations can be listed with \d:

\d

A trailing character can be used in the form of \d{E,i,m,s,t,v} to specify a type of relation (see the full documentation for more information). This is often combined with a “pattern” which we can set to the name of a schema we want to query. For example, this will list all tables and views inside of my_schema:

\dtv my_schema.

And a particular relation can be described with (and try using a \d+ instead to get even more information):

\d my_schema.my_table

Quick reference

Now we’ve introduced a lot of esoteric commands here that might be hard to remember, but luckily there’s an easy trick. Psql has a few different internal help mechanisms, and we can use \? to invoke a very succinct help menu that contains a quick reference for every “backslash command”:

\?

If you’re going to take one thing away from this article, make it \?.

Search path

One other important piece to mention is the search_path setting, which has a few important functions:

1. When trying to resolve a symbol without a schema prefix, each schema in the search_path will be tried in turn. Schemas outside of the search_path will not be tried, and a schema-less symbol that belongs to one of them will not be resolved.
2. When listing relations with \d and without providing a search pattern, only schemas in search_path will be shown.
3. May subtly shift behavior in a few other places. For example, in Redshift pg_table_def will only show information for tables contained in schemas that are present in search_path.

search_path can be set as follows (and note that this can also be placed in your ~/.psqlrc):

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