psql db

Running \? within a psql database gives a whole list of commands that come in handy when performing various kinds of tasks.

  $ psql
  psql (13.0)
  Type "help" for help.

  wangonya=# \?
  General
    \copyright             show PostgreSQL usage and distribution terms
    \crosstabview [COLUMNS] execute query and display results in crosstab
    \errverbose            show most recent error message at maximum verbosity

    .....

But what are the queries executed when these commands run?

I was recently tasked to write a bash script that loops through every schema on a database and does a pg_dump on it for backup then uploads the backups to an external server.

Running \dn gives a list of schemas but it also gives the owners, which I didn't need in my case.

  wangonya=# \dn
    List of schemas
    Name  |  Owner
  --------+----------
   public | wangonya
   x      | wangonya
   y      | wangonya
  (3 rows)

I only needed the schema names so I had to look for a way to edit the query run by \dn to only return names.

From the psql docs:

-E > --echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

Running psql with the -E flag then running the /dn command gives this result:

  $ psql -E

  wangonya-# \dn
  ********* QUERY **********
  SELECT n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;
  **************************

    List of schemas
    Name  |  Owner
  --------+----------
   public | wangonya
   x      | wangonya
   y      | wangonya
  (3 rows)

With the underlying query executed on running the slash command, I was able to edit it get the desired result.