postgresql psql 的替代输出格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9604723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alternate output format for psql
提问by user100464
I am using PostgreSQL 8.4 on Ubuntu. I have a table with columns c1through 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.
我在 Ubuntu 上使用 PostgreSQL 8.4。我有一个表格，其中的列c1通过cN. 列足够宽，选择所有列会导致一行查询结果多次换行。因此，输出很难阅读。
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, e.g.
当查询结果只有几行时，如果我可以查看查询结果，使每行的每一列都在单独的行上，那将很方便，例如
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 设置可以让我做这样的事情？
回答by user100464
I just needed to spend more time staring at the documentation. This command:
我只需要花更多时间看文档。这个命令：
\x on
will do exactly what I wanted. Here is some sample output:
会做我想做的。这是一些示例输出：
select * from dda where u_id=24 and dda_is_deleted='f';
-[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
dda_id | 1121
u_id | 24
ab_id | 10304
dda_type | CHECKING
dda_status | PENDING_VERIFICATION
dda_is_deleted | f
dda_verify_op_id | 44938
version | 2
created | 2012-03-06 21:37:50.585845
modified | 2012-03-06 21:37:50.593425
c_id |
dda_nickname |
dda_account_name |
cu_id | 1
abd_id |
回答by notapatch
(New) Expanded Auto Mode: \x auto
（新）扩展的自动模式：\x auto
New for Postgresql 9.2; PSQL automatically fits records to the width of the screen. previously you only had expanded mode on or off and had to switch between the modes as necessary.
Postgresql 9.2 的新增功能；PSQL 自动使记录适合屏幕宽度。以前，您只能打开或关闭扩展模式，并且必须根据需要在模式之间切换。
- If the record can fit into the width of the screen; psql uses normal formatting.
- If the record can notfit into the width of the screen; psql uses expanded mode.
- 如果记录能适应屏幕的宽度；psql 使用正常格式。
- 如果记录可以不适合屏幕的宽度; psql 使用扩展模式。
To get this use: \x auto
要获得此用途：\x auto
Postgresql 9.5 Documentation on PSQL command.
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 autocommand on startup by adding it to .psqlrcin your home folder and restarting psql. Look under 'Files' section in the psql doc for more info.
\x auto通过将命令添加到.psqlrc您的主文件夹并重新启动 psql来配置启动时的命令。查看 psql 文档中的“文件”部分以获取更多信息。
~/.psqlrc
~/.psqlrc
\x auto
回答by Bryce
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 auto”选项仅在“需要时”切换到逐行显示。
-[ 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. Here's an example of wrapped:
较旧的“\pset 格式包装”的相似之处在于它尝试将数据整齐地放在屏幕上，但如果标题不适合，则回退到未对齐。下面是一个包裹的例子：
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
回答by Dineshkumar
One interesting thing is we can view the tables horizontally, without folding. we can use PAGERenvironment variable. psql makes use of it. you can set
一件有趣的事情是我们可以水平查看表格，无需折叠。我们可以使用PAGER环境变量。psql 使用了它。你可以设置
export PAGER='/usr/bin/less -S'
or just less -Sif its already availble in command line, if not with the proper location. -S to view unfolded lines. you can pass in any custom viewer or other options with it.
或者只是less -S如果它已经在命令行中可用，如果没有正确的位置。-S 查看展开的线条。您可以传入任何自定义查看器或其他选项。
I've written more in Psql Horizontal Display
我在Psql 水平显示中写了更多
回答by Tombart
pspgis a simple tool that offers advanced table formatting, horizontal scrolling, search and many more features.
pspg是一个简单的工具，提供高级表格格式、水平滚动、搜索和更多功能。
git clone https://github.com/okbob/pspg.git
cd pspg
./configure
make
make install
then make sure to update PAGERvariable e.g. in your ~/.bashrc
然后确保更新PAGER变量，例如在您的~/.bashrc
export PAGER="pspg -s 6"
where -sstands for color scheme (1-14). If you're using pgdg repositoriessimply install a package (on Debian-like distribution):
其中-s代表配色方案 ( 1-14)。如果您使用pgdg 存储库，只需安装一个软件包（在类似 Debian 的发行版上）：
sudo apt install pspg
回答by Wexxor
Also be sure to check out \H, which toggles HTML output on/off. 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.
还要确保检查\H，它可以打开/关闭HTML 输出。在控制台上不一定容易阅读，但对于转储到文件（参见 \o）或粘贴到编辑器/浏览器窗口进行查看很有趣，尤其是多行相对复杂的数据。
回答by purushothaman poovai
you can use the zenityto displays the query output as html table.
您可以使用zenity将查询输出显示为 html 表。
first implement bash script with following code:
cat > '/tmp/sql.op'; zenity --text-info --html --filename='/tmp/sql.op';
save it like mypager.sh
Then export the environment variable PAGER by set full path of the script as value.
for example:- export PAGER='/path/mypager.sh'
Then login to the psql program then execute the command \H
And finally execute any query,the tabled output will displayed in the zenity in html table format.
首先使用以下代码实现bash脚本：
猫 > '/tmp/sql.op'; zenity --text-info --html --filename='/tmp/sql.op';
将其保存为mypager.sh
然后通过将脚本的完整路径设置为值来导出环境变量 PAGER。
例如：- export PAGER='/path/mypager.sh'
然后登录psql程序然后执行命令\H
最后执行任何查询，表格输出将以html表格格式显示在zenity中。
