- 语法
"\pset"命令的语法如下:
\pset [option [value] ]
postgres@linuxpg51:5432=#\pset
border 1
columns 0
csv_fieldsep ','
expanded off
fieldsep '|'
fieldsep_zero off
footer on
format aligned
linestyle ascii
null ''
numericlocale off
pager 1
pager_min_lines 0
recordsep '\n'
recordsep_zero off
tableattr
title
tuples_only off
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
根据命令后面“option”和“value”的不同可以设置很多种不同的输出格式,这里只介绍一些常用的用法。
- 默认情况下,psql中执行SQL语句后输出的内容是只有内边框的表格:
postgres@linuxpg51:5432=#select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
(3 rows)
postgres@linuxpg51:5432=#
- 如果要像MySQL中一样输出带有内外边框的表格内容,可以用命令"\pset boder 2"来实现,示例如下:
postgres@linuxpg51:5432=#\pset border 2
Border style is 2.
postgres@linuxpg51:5432=#select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
(3 rows)
postgres@linuxpg51:5432=#
- 当然也可以用"\pset boder 0"命令输出不带任何边框的内容,示例如下:
postgres@linuxpg51:5432=#\pset border 0
Border style is 0.
postgres@linuxpg51:5432=#select * from emp;
empno ename job mgr hiredate sal comm deptno
----- ------ --------- ---- ---------- ------- ------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
(3 rows)
postgres@linuxpg51:5432=#
综上所述,“\pset”命令设置边框的用法如下:
·\pset border 0:表示输出内容无边框。
·\pset border 1:表示输出内容只有内边框。
·\pset border 2:表示输出内容内外都有边框。
psql中默认的输出格式是"\pset border 1"。
- 不管输出的内容加不加边框,内容本身都是对齐的,是为增强数据的可读性而专门格式化过的,而有时我们需要把命令的结果输出为其他程序可以读取的文件,如以逗号分隔或以Tab分隔的文本文件,这时就需要用到"\pset format unaligned"命令了,示例如下:
postgres@linuxpg51:5432=#\pset format unaligned
Output format is unaligned.
postgres@linuxpg51:5432=#select * from emp;
empno|ename|job|mgr|hiredate|sal|comm|deptno
7369|SMITH|CLERK|7902|1980-12-17|800.00||20
7499|ALLEN|SALESMAN|7698|1981-02-20|1600.00|300.00|30
7521|WARD|SALESMAN|7698|1981-02-22|1250.00|500.00|30
(3 rows)
postgres@linuxpg51:5432=#
- 默认分隔符是"|",我们可以用命令"\pset fieldsep"来设置分隔符
改成Tab分隔符的方法如下:
\pset fieldsep '\t'
postgres@linuxpg51:5432=#\pset format unaligned
Output format is unaligned.
postgres@linuxpg51:5432=#\pset fieldsep '\t'
Field separator is " ".
postgres@linuxpg51:5432=#select empno,ename from emp;
empno ename
7369 SMITH
7499 ALLEN
7521 WARD
(3 rows)
改成","分隔符的方法如下:
postgres@linuxpg51:5432=#\pset fieldsep ','
Field separator is ",".
postgres@linuxpg51:5432=#select * from emp;
empno,ename,job,mgr,hiredate,sal,comm,deptno
7369,SMITH,CLERK,7902,1980-12-17,800.00,,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600.00,300.00,30
7521,WARD,SALESMAN,7698,1981-02-22,1250.00,500.00,30
(3 rows)