DEV Community

Kelvin Wangonya
Kelvin Wangonya

Posted on • Updated on <time datetime="2020-12-13T06:58:43Z" class="date">Dec 13, 2020</time> • Originally published at wangonya.com

What happens when you run PSQL slash commands?

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

  .....
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

Discussion (1)

Collapse
mtancoigne profile image
Manuel Tancoigne

Simple and useful!