Show the SQL commands psql uses internally to show database meta-information

Scenario: you want to know more about the system catalogues or want to know more about how psql interacts with the backend. psql uses a variety of SQL statements to generate the output for the various \d slash functions but the output is usually hidden.

Solution:

  • start psql with the -E command line option
  • (while psql is running) set ECHO_HIDDEN to a non-zero integer value

Either will cause psql to display the SQL it generates. Note that if ECHO_HIDDEN is set to noexec, SQL will be displayed but not execute it (this will effectively disable the informational slash commands).

elephant~$ psql -U postgres -E
psql (9.2.3)
Type "help" for help.

postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                                     List of databases
      Name       |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------------+-----------+----------+-------------+-------------+-----------------------
 template0       | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |           |          |             |             | postgres=CTc/postgres
 template1       | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
                 |           |          |             |             | =c/postgres
 testdb          | testuser  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(3 rows)

postgres=# \set ECHO_HIDDEN noexec
postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************