显示 psql 内部用于显示数据库元信息的 SQL 命令

原创于 2026-06-21 09:12:34 发布 · 15 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#数据库 #sql

场景：你想了解更多关于系统目录的信息，或者想了解psql如何与后端交互。 psql 使用各种 SQL 语句来生成各种命令的输出\d斜杠命令，但输出通常是隐藏的。

解决方案

使用命令行选项-E启动 psql
(在 psql 运行时) 设置ECHO_HIDDEN为非零整数值

任一方法都会导致psql显示其生成的 SQL。请注意，如果 ECHO_HIDDEN 设置为 noexec，SQL 将被显示但不会执行（这将有效地禁用信息性斜杠命令）。

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;
**************************