psql元命令的使用

作者：瀚高PG实验室 （Highgo PG Lab）- Kalath

psql中的元命令是指以反斜线开头的命令。例如，\db为查看表空间。\dt tablename为查看表大小。

当使用psql连接数据库时，-E选项可以获取元命令的SQL代码，例如：

postgres@hg:~$ psql -E
psql (10.2)
Type "help" for help.

postgres=# \db
********* QUERY **********
SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************

       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
pg_default | postgres |
pg_global  | postgres |
(2 rows)

使用\?可以列出所有的元命令以及这些元命令的说明及语法。例如：

postgres=# \?
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
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds
...
...
...

psql中，使用-A设置非对齐输出模式。例如：

postgres@hg:~$ psql -A
psql (10.2)
Type "help" for help.
  
postgres=# select * from test;
id|passstring|pass1|pass2|pass3
1|abcdefghijklmnopqrstu|:nDNL76IgtKSYo|:dmlF6XruXN2IA|:bgxgYjIs/NhT.
2|hoge|:Bra44iY.vmPzA|:003uyQxlYQEgE|:A0zs16Dq9cdcI
(2 rows)

使用-t只显示记录数据（不显示字段名称和返回的结果集行数）。例如：

postgres@hg:~$ psql -t
psql (10.2)
Type "help" for help.

postgres=# select * from test;
  1 | abcdefghijklmnopqrstu | :nDNL76IgtKSYo | :dmlF6XruXN2IA | :bgxgYjIs/NhT.
  2 | hoge                  | :Bra44iY.vmPzA | :003uyQxlYQEgE | :A0zs16Dq9cdcI

-t参数通常和-A参数一起使用，这时仅返回数据本身。例如：

postgres@hg:~$ psql -At
psql (10.2)
Type "help" for help.

postgres=# select * from test;
1|abcdefghijklmnopqrstu|:nDNL76IgtKSYo|:dmlF6XruXN2IA|:bgxgYjIs/NhT.
2|hoge|:Bra44iY.vmPzA|:003uyQxlYQEgE|:A0zs16Dq9cdcI