Postgresql的psql特殊功能与技巧

1、\x列模式显示数据

postgres=# select * from u1;
 id |    name    
----+------------
  1 | aa        
  2 | xx        
  3 | yy        
(3 rows)

postgres=# \x
Expanded display is on.
postgres=# select * from u1;
-[ RECORD 1 ]----
id   | 1
name | aa        
-[ RECORD 2 ]----
id   | 2
name | xx        
-[ RECORD 3 ]----
id   | 3
name | yy      

2、tab补全

postgres=# show log 按tab键
log_autovacuum_min_duration  log_filename                 log_replication_commands
log_checkpoints              logging_collector            log_rotation_age
log_connections              log_hostname                 log_rotation_size
log_destination              log_line_prefix              log_statement
log_directory                log_lock_waits               log_statement_stats
log_disconnections           log_min_duration_statement   log_temp_files
log_duration                 log_min_error_statement      log_timezone
log_error_verbosity          log_min_messages             log_truncate_on_rotation
log_executor_stats           log_parser_stats             
log_file_mode                log_planner_stats            
postgres=# show log

3、watch反复执行当前SQL

[postgre10sz@fdb01 ~]$ psql -d postgres postgre10sz
psql (10.5)
Type "help" for help.

postgres=# select now();
              now              
-------------------------------
 2019-08-26 15:34:34.694007+08
(1 row)

postgres=# \watch 3
Mon 26 Aug 2019 03:34:39 PM CST (every 3s)

              now              
-------------------------------
 2019-08-26 15:34:39.285699+08
(1 row)

Mon 26 Aug 2019 03:34:42 PM CST (every 3s)

              now              
-------------------------------
 2019-08-26 15:34:42.291282+08
(1 row)

Mon 26 Aug 2019 03:34:45 PM CST (every 3s)

              now              
-------------------------------
 2019-08-26 15:34:45.297538+08
(1 row)

4、SQL执行时间\timing

(postgre10sz@[local]:54310)-[postgres]-#\timing
Timing is on.

(postgre10sz@[local]:54310)-[postgres]-#select count(*) from tcopy2;
 count 
-------
     4
(1 row)

**Time: 0.368 ms**

5、.psqlrc显示变量

[postcert@szts03 ~]$ vi .psqlrc 
\set PROMPT1 '(%n@%M:%>)-[%/]-%#'

效果

(postcert@[local]:54311)-[postgres]-#


[postcert@szts03 ~]$ vi .psqlrc 
\set PROMPT2

效果：隐藏了表头

(postcert@[local]:54311)-[postgres]-#\d
 public | t1   | table | postcert

(postcert@[local]:54311)-[postgres]-#\d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postcert
(1 row)

6、\pset 输出的显示格式

\pset border 0 输出无边框

postgres=# \pset border 0;
Border style is 0.
postgres=# select * from u1;
id    name    
-- ----------
 1 aa        
 2 xx        
 3 yy        
(3 rows)

\pset border 1 输出有内边框

postgres=# \pset border 1;
Border style is 1.
postgres=# select * from u1;
 id |    name    
----+------------
  1 | aa        
  2 | xx        
  3 | yy        
(3 rows)


\pset border 2 输出有内外边框

postgres=# \pset border 2
Border style is 2.
postgres=# select * from u1;
+----+------------+
| id |    name    |
+----+------------+
|  1 | aa         |
|  2 | xx         |
|  3 | yy         |
+----+------------+
(3 rows)