What happens when you run PSQL slash commands?
Dec 10, 2020
Read time: 2 minutes
\? 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.
\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:
Echo the actual queries generated by
\dand 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.