psql的备用输出格式

本文翻译自：Alternate output format for psql

I am using PostgreSQL 8.4 on Ubuntu. 我在Ubuntu上使用PostgreSQL 8.4。 I have a table with columns c1 through cN . 我有一个表，其中的列c1到cN 。 The columns are wide enough that selecting all columns causes a row of query results to wrap multiple times. 列足够宽，选择所有列都会导致一行查询结果多次包装。 Consequently, the output is hard to read. 因此，输出难以读取。

When the query results constitute just a few rows, it would be convenient if I could view the query results such that each column of each row is on a separate line, eg 当查询结果仅占几行时，如果我可以查看查询结果以使每一行的每一列都位于单独的行（例如，

 c1: <value of row 1's c1>
 c2: <value of row 1's c1>
 ...
 cN: <value of row 1's cN>
 ---- some kind of delimiter ----
 c1: <value of row 2's c1>
 etc.

I am running these queries on a server where I would prefer not to install any additional software. 我在不希望安装任何其他软件的服务器上运行这些查询。 Is there a psql setting that will let me do something like that? 是否有一个psql设置可以让我做类似的事情？

---

#1楼

参考：https://stackoom.com/question/EiCT/psql的备用输出格式

---

#2楼

Also be sure to check out \\H, which toggles HTML output on/off. 另外，请务必检查出\\ H，它会打开/关闭HTML输出。 Not necessarily easy to read at the console, but interesting for dumping into a file (see \\o) or pasting into an editor/browser window for viewing, especially with multiple rows of relatively complex data. 在控制台上不一定很容易阅读，但是对于转储到文件中（请参见\\ o）或粘贴到编辑器/浏览器窗口中进行查看（特别是包含多行相对复杂的数据）尤其有趣。

---

#3楼

(New) Expanded Auto Mode: \\x auto （新）扩展的自动模式：\\ x自动

New for Postgresql 9.2; Postgresql 9.2的新增功能； PSQL automatically fits records to the width of the screen. PSQL自动使记录适合屏幕的宽度。 previously you only had expanded mode on or off and had to switch between the modes as necessary. 以前，您只能打开或关闭扩展模式，并且必须根据需要在模式之间进行切换。

• If the record can fit into the width of the screen; 记录是否适合屏幕宽度； psql uses normal formatting. psql使用常规格式。
• If the record can not fit into the width of the screen; 如果记录不适合屏幕的宽度; psql uses expanded mode. psql使用扩展模式。

To get this use: \\x auto 要使用此功能： \\ x auto

Postgresql 9.5 Documentation on PSQL command. PostgreSQL 9.5有关PSQL命令的文档。

---

Wide screen, normal formatting: 宽屏，正常格式：

 id | time  |       humanize_time             | value 
----+-------+---------------------------------+-------
  1 | 09:30 |  Early Morning - (9.30 am)      |   570
  2 | 11:30 |  Late Morning - (11.30 am)      |   690
  3 | 13:30 |  Early Afternoon - (1.30pm)     |   810
  4 | 15:30 |  Late Afternoon - (3.30 pm)     |   930
(4 rows)

---

Narrow screen, expanded formatting: 窄屏，扩展格式：

-[ RECORD 1 ]-+---------------------------
id            | 1
time          | 09:30
humanize_time | Early Morning - (9.30 am)
value         | 570
-[ RECORD 2 ]-+---------------------------
id            | 2
time          | 11:30
humanize_time | Late Morning - (11.30 am)
value         | 690
-[ RECORD 3 ]-+---------------------------
id            | 3
time          | 13:30
humanize_time | Early Afternoon - (1.30pm)
value         | 810
-[ RECORD 4 ]-+---------------------------
id            | 4
time          | 15:30
humanize_time | Late Afternoon - (3.30 pm)
value         | 930

---

How to start psql with \\x auto? 如何使用\\ x auto启动psql？

Configure \\x auto command on startup by adding it to .psqlrc in your home folder and restarting psql. 通过将\\x auto命令添加到主文件夹中的.psqlrc并重新启动psql，在启动时配置\\x auto命令。 Look under 'Files' section in the psql doc for more info . 在psql doc中的“文件”部分下查找更多信息 。

~/.psqlrc 〜/ .psqlrc

\x auto

---

#4楼

You have so many choices, how could you be confused :-)? 您有很多选择，您怎么会困惑:-)？ The main controls are: 主要控件是：

# \pset format
# \H
# \x
# \pset pager off

Each has options and interactions with the others. 每个人都有选择和与其他人的互动。 The most automatic options are: 最自动的选项是：

# \x off;\pset format wrapped
# \x auto

The newer "\\x auto" option switches to line-by-line display only "if needed". 较新的“ \\ x自动”选项仅在“需要时”切换到逐行显示。

-[ RECORD 1 ]---------------
id          | 6
description | This is a gallery of oilve oil brands.
authority   | I love olive oil, and wanted to create a place for
reviews and comments on various types.
-[ RECORD 2 ]---------------
id          | 19
description | XXX Test A 
authority   | Testing

The older "\\pset format wrapped" is similar in that it tries to fit the data neatly on screen, but falls back to unaligned if the headers won't fit. 较旧的“ \\ pset包装格式”与之类似，因为它试图将数据整齐地显示在屏幕上，但是如果标题不适合，则会退回到未对齐状态。 Here's an example of wrapped: 这是包装的示例：

 id |          description           |            authority            
----+--------------------------------+---------------------------------
  6 | This is a gallery of oilve     | I love olive oil, and wanted to
    ; oil brands.                    ;  create a place for reviews and
    ;                                ;  comments on various types.
 19 | Test Test A                    | Testing

---

#5楼

One interesting thing is we can view the tables horizontally, without folding. 一件有趣的事情是我们可以水平查看桌子，而无需折叠。 we can use PAGER environment variable. 我们可以使用PAGER环境变量。 psql makes use of it. psql使用它。 you can set 你可以设置

export PAGER='/usr/bin/less -S'

or just less -S if its already availble in command line, if not with the proper location. 如果在命令行中已经可用，则使用less -S或less -S （如果没有正确的位置）。 -S to view unfolded lines. -S查看展开的线。 you can pass in any custom viewer or other options with it. 您可以将任何自定义查看器或其他选项传递给它。

I've written more in Psql Horizontal Display 我在Psql水平显示中写了更多

---

#6楼

you can use the zenity to displays the query output as html table. 您可以使用zenity将查询输出显示为html表。

• first implement bash script with following code: 首先用以下代码实现bash脚本：

  cat > '/tmp/sql.op'; cat>'/tmp/sql.op'; zenity --text-info --html --filename='/tmp/sql.op'; zenity --text-info --html --filename ='/ tmp / sql.op';

  save it like mypager.sh 像mypager.sh一样保存

• Then export the environment variable PAGER by set full path of the script as value. 然后通过将脚本的完整路径设置为值来导出环境变量PAGER。

  for example:- export PAGER='/path/mypager.sh' 例如： -export PAGER ='/ path / mypager.sh'

• Then login to the psql program then execute the command \\H 然后登录到psql程序，然后执行命令\\ H

• And finally execute any query,the tabled output will displayed in the zenity in html table format. 最后执行任何查询，表格输出将以zenity格式以html表格格式显示。