在使用psql客户端工具来管理、维护PostgreSQL数据库的过程中，经常遇到一些格式的问题：输出结果被折叠覆盖。对于熟悉Oracle数据库的sql*plus客户端工具的DBA来说，这简直是梦魇。这里，收录一些psql客户端工具的设置。
1 输出结果被折叠覆盖
ai_task1=> \d t_ai_prd_task_field
Table "ai_task1.t_ai_prd_task_field"
Column | Type | Modifiers
---------------------------+--------------------------------+-----------
id | character varying(32) | not null
task_id | character varying(32) |
job_field_id | character varying(32) |
item_id | character varying(32) |
...
...
sort_no | smallint |
template_display_name | character varying(128) |
Indexes:
"t_ai_prd_task_field_pkey" PRIMARY KEY, btree (id)
"idx_task_field_applicant_name" btree (applicant_name)
"idx_task_field_bill_code" btree (bill_code)
"idx_task_field_item_id" btree (item_id)
"idx_task_field_job_field_id" btree (job_field_id)
"idx_task_field_job_id" btree (job_id)
"idx_task_field_packet_code" btree (packet_code)
"idx_task_field_status" btree (status)
"idx_task_field_submit_time" btree (submit_time)
"idx_task_field_task_id" btree (task_id)
tegory, cust_category, template_name, input_source)name, applicant_name, task_ca--More--
ai_task1=>
查看表结构时，后面带出index信息中，对于最后一行，完全不知道索引的相关信息，取而代之的是–More–，这不要命么？可以通过在命令行上设置，\pset pager off。
ai_task1=> \pset pager off
Pager usage is off.
ai_task1=> \d t_ai_prd_task_field
Table "ai_task1.t_ai_prd_task_field"
Column | Type | Modifiers
---------------------------+--------------------------------+-----------
id | character varying(32) | not null
task_id | character varying(32) |
job_field_id | character varying(32) |
item_id | character varying(32) |
...
...
sort_no | smallint |
template_display_name | character varying(128) |
Indexes:
"t_ai_prd_task_field_pkey" PRIMARY KEY, btree (id)
"idx_task_field_applicant_name" btree (applicant_name)
"idx_task_field_bill_code" btree (bill_code)
"idx_task_field_item_id" btree (item_id)
"idx_task_field_job_field_id" btree (job_field_id)
"idx_task_field_job_id" btree (job_id)
"idx_task_field_packet_code" btree (packet_code)
"idx_task_field_status" btree (status)
"idx_task_field_submit_time" btree (submit_time)
"idx_task_field_task_id" btree (task_id)
"idx_task_field_union_gin" gin (template_field_name, applicant_name, task_category, cust_category, template_name, input_source)
ai_task1=>
瞬间变得神清气爽，清清楚楚。
2 设置输出回显的linestyle和边框
ai_task1=> \pset border 2
Border style is 2.
ai_task1=> \pset linestyle unicode
Line style is unicode.
ai_task1=> \d t_07
Table "ai_task1.t_07"
┌───────────────────────────┬────────────────────────┬───────────┐
│ Column │ Type │ Modifiers │
├───────────────────────────┼────────────────────────┼───────────┤
│ bill_code │ character varying(12) │ │
│ template_no │ character varying(32) │ │
│ template_field_identifier │ character varying(64) │ │
│ company_name │ character varying(255) │ │
│ submit_value │ character varying │ │
└───────────────────────────┴────────────────────────┴───────────┘
ai_task1=>
整齐划一、干净好看。
3 换行截断
postgres=# select * from t;
id | content
----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | If you are used to psql, you may notice in the picture above, some content is wrapped. This is \pset format wrapped option.
2 | Some commands take an SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotes, paired double quotes reduce to a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.+
| +
|
(2 rows)
postgres=#
对于terminal window比较小的场景下，这个查询结果并不够友好。可以，通过设置：\pset format wrapped
postgres=# \pset format wrapped
Output format is wrapped.
postgres=# select * from t;
id | content
----+--------------------------------------------------------------------------------------
1 | If you are used to psql, you may notice in the picture above, some content is wrappe.
|.d. This is \pset format wrapped option.
2 | Some commands take an SQL identifier (such as a table name) as argument. These argum.
|.ents follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while.
|. double quotes (") protect letters from case conversion and allow incorporation of w.
|.hitespace into the identifier. Within double quotes, paired double quotes reduce to .
|.a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted.
|. as fooBARbaz, and "A weird"" name" becomes A weird" name. +
| +
|
(2 rows)
postgres=#
4 设置编辑器
通过设置执行psql命令的用户的环境变量EDITOR=vi,可以在psql命令行上通过执行\e 来调用vi编辑SQL语句。需要注意的是：
a \e 只能编辑修改最后1条SQL语句；
b \e 不能编辑从命令行读取的SQL脚本中的命令，这个有别于Oracle数据库。
c \e 如果出现E437: terminal capability “cm” required时，有可能是用户的环境变量TERM没设置，修改一下即可。
[enterprisedb@ppasdev ~]$ export EDITOR=vi [enterprisedb@ppasdev ~]$ export TERM=xterm [enterprisedb@ppasdev ~]$ sh local psql.bin (9.3.11.33) Type "help" for help. postgres=# select * from t; .. .. postgres=# \e 调用vi编辑SQL语句。
5 小结
可以把关于psql的设置，配置到需要执行psql命令行工具用户的家目录下的名为.psqlrc文件中：
[enterprisedb@ecs-public2 ~]$ cat ~/.psqlrc \pset pager off \pset border 2 \pset linestyle unicode \pset format wrapped [enterprisedb@ecs-public2 ~]$
可以把关于环境变量的设置，配置到用户的家目录下的.bash_profile中：
[enterprisedb@ecs-public2 ~]$ cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/ppas/9.3as/bin
export PATH
export LANG=en_US.UTF-8
export PGDATA=/ppas/9.3as/data
export EDITOR=vi
export TERM=xterm
[enterprisedb@ecs-public2 ~]$
这些相关设置，更为具体的可以参考官方文档：https://www.postgresql.org/docs/9.1/static/app-psql.html
这里，只是做个简单记录，解决我个人目前遇到的几个棘手问题，后续再读文档时更新本文。