1. 云图网首页
  2. 技术专区
  3. 数据库

postgres中psql使用和设置输出结果

• 数据库


psql官方文档-12

如果在当前shell 下，如果设定 export PGPASSWORD=’postgres密码’ 环境变量，可以不用每次执行sql 语句或者导入一个sql 文件都输入一次密码的麻烦了。

1.设置输出结果边框

默认:/pset border 0

test=# select * from weather ;
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

/pset border 0

test=# /pset border 2
Border style is 2.
test=# select * from weather ;
+---------------+---------+---------+------+------------+
|     city      | temp_lo | temp_hi | prcp |    date    |
+---------------+---------+---------+------+------------+
| San Francisco |      46 |      50 | 0.25 | 1994-11-27 |
| San Francisco |      33 |      60 | 0.15 | 1994-11-27 |
| San 66        |      66 |      45 | 0.15 | 1994-11-27 |
| San Francisco |      39 |      53 |    0 | 1994-11-29 |
| Hayward       |      33 |      50 |      | 1994-11-29 |
+---------------+---------+---------+------+------------+
(5 rows)

/pset border 0

test=# /pset border 0
Border style is 0.
test=# select * from weather ;
    city      temp_lo temp_hi prcp    date    
------------- ------- ------- ---- ----------
San Francisco      46      50 0.25 1994-11-27
San Francisco      33      60 0.15 1994-11-27
San 66             66      45 0.15 1994-11-27
San Francisco      39      53    0 1994-11-29
Hayward            33      50      1994-11-29
(5 rows)

2.输出结果到文件

客户端/o

不指定路径，默认输出到home家目录下

最后一个表示关闭文件输出

test=# /o test.txt
test=# select * from weather ;
test=# /o

终端-o

psql -U postgres -d test -o t.txt -c 'SELECT * FROM weather;'

3.控制表头

客户端/t

# 表头开启
test=# /t
Tuples only is off.
test=# select * from weather ;

     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

# 表头关闭
test=# /t
Tuples only is on.
test=# select * from weather ;

 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29

终端-t

~ psql -U postgres -d test -t -c 'SELECT * FROM weather;'
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29

~ psql -U postgres -d test -c 'SELECT * FROM weather;' 
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

4.纵向横向显示

客户端/x

# 纵向输出
test=# /x
Expanded display is on.
test=# select * from weather where city='San 66';
-[ RECORD 1 ]-------
city    | San 66
temp_lo | 66
temp_hi | 45
prcp    | 0.15
date    | 1994-11-27

# 原始(横向输出)
test=# /x
Expanded display is off.
test=# select * from weather where city='San 66';
  city  | temp_lo | temp_hi | prcp |    date    
--------+---------+---------+------+------------
 San 66 |      66 |      45 | 0.15 | 1994-11-27
(1 row)

终端执行-x

psql -U postgres -d test -x -c "select * from weather where city='San 66'"

psql -U postgres -d test -c '/x' -c "select * from weather where city='San 66'"

5.对齐设置

对齐

客户端

test=# /pset format aligned
Output format is aligned.
test=# select * from weather;

     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

不对齐

客户端

test=# /pset format unaligned
Output format is unaligned.
test=# select * from weather;

city    temp_lo temp_hi prcp    date
San Francisco   46      50      0.25    1994-11-27
San Francisco   33      60      0.15    1994-11-27
San 66  66      45      0.15    1994-11-27
San Francisco   39      53      0       1994-11-29
Hayward 33      50              1994-11-29
(5 rows)

终端-A

psql -U postgres -d test -A -c 'SELECT * FROM weather;' 

city|temp_lo|temp_hi|prcp|date
San Francisco|46|50|0.25|1994-11-27
San Francisco|33|60|0.15|1994-11-27
San 66|66|45|0.15|1994-11-27
San Francisco|39|53|0|1994-11-29
Hayward|33|50||1994-11-29
(5 rows)

6.输出格式

csv格式

客户端

test=# /pset format csv
Output format is csv.
test=# select * from weather;
city,temp_lo,temp_hi,prcp,date
San Francisco,46,50,0.25,1994-11-27
San Francisco,33,60,0.15,1994-11-27
San 66,66,45,0.15,1994-11-27
San Francisco,39,53,0,1994-11-29
Hayward,33,50,,1994-11-29

终端--csv

psql --csv -U postgres -d test <<EOF
SELECT * FROM weather;
EOF
Password for user postgres: 
city,temp_lo,temp_hi,prcp,date
San Francisco,46,50,0.25,1994-11-27
San Francisco,33,60,0.15,1994-11-27
San 66,66,45,0.15,1994-11-27
San Francisco,39,53,0,1994-11-29
Hayward,33,50,,1994-11-29

html格式

客户端

test=# /pset format html
Output format is html.
test=# select * from weather;
<table border="1">
  <tr>
    <th align="center">city</th>
    <th align="center">temp_lo</th>
    <th align="center">temp_hi</th>
    <th align="center">prcp</th>
    <th align="center">date</th>
  </tr>
 ......
</table>
<p>(5 rows)<br />
</p>

终端-H

psql -U postgres -d test -H -c "select * from weather;"

7.设置分隔符/pset fieldsep '|'

需要在/pset format unaligned下才能成功

客户端

test=# /pset fieldsep '//'
Field separator is "//".
test=# select * from weather;
city//temp_lo//temp_hi//prcp//date
San Francisco//46//50//0.25//1994-11-27
San Francisco//33//60//0.15//1994-11-27
San 66//66//45//0.15//1994-11-27
San Francisco//39//53//0//1994-11-29
Hayward//33//50////1994-11-29
(5 rows)

终端-F

~ psql -U postgres -d test -A -F '//' -c 'SELECT * FROM weather;'
city//temp_lo//temp_hi//prcp//date
San Francisco//46//50//0.25//1994-11-27
San Francisco//33//60//0.15//1994-11-27
San 66//66//45//0.15//1994-11-27
San Francisco//39//53//0//1994-11-29
Hayward//33//50////1994-11-29
(5 rows)

8.执行外部sql文件

/i

# test=# /i t.sql 默认找用户家目录下的文件
test=# /i /Users/lxd670/t.sql 
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

终端执行sql文件-f

~ psql -U postgres -d test -f t.sql  
Password for user postgres: ******
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

9.执行外部sql语句

终端执行sql语句EOF

psql -U postgres -d test <<EOF
SELECT * FROM weather;
EOF
Password for user postgres: ******
     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

终端执行sql语句-c

psql -U postgres -d test -c 'SELECT * FROM weather;'

     city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      33 |      60 | 0.15 | 1994-11-27
 San 66        |      66 |      45 | 0.15 | 1994-11-27
 San Francisco |      39 |      53 |    0 | 1994-11-29
 Hayward       |      33 |      50 |      | 1994-11-29
(5 rows)

原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/275093.html

赞 (0)
0
上一篇 2022年7月18日
下一篇 2022年7月18日

相关推荐

发表回复

登录后才能评论