PSQL工具的一些快捷命令和使用介绍(不定期更新)

1. psql -l

假如不提前知道PG内有多少个库，可以通过psql -l  命令来看

[postgres@centos7 pgdata]$ psql -l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)

注意里面存在的template0与template1，属于模板数据库

template1：当用户在创建数据库时，默认是从模板数据库template1中克隆出来的，所以通常可以定制template1中的内容，比如往其中添加一些表或者函数，后续创建的数据库就会继承

template0：是最简化的数据库，创建数据库时，假如显式指定从此数据库中继承，将会创建一个最简化的数据库

2. \l 

或者可以进入psql，使用\l查看数据库

[postgres@centos7 pgdata]$ psql
psql (15.1)
Type "help" for help.

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)

3. \c

切换数据库 \c

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".

4. \d

展示详细信息 \d

\d可以展示每个匹配关系的详细信息，例如（表、视图、索引、序列）等

4.1 如果\d命令什么都不带，将列出数据库中的所有表

4.2 \d后面跟表名，显示这个表的结构定义

4.3 \d后面跟索引名,显示索引的信息

4.4 跟通配符模糊匹配表名

，例如“*”或“?”,

4.5 \d+,将显示比\d更详细的命令

包括:任何与表列关联的注释,以及表中出现的OID

db_test=# \d+ dongsc.t1
                                                 Table "dongsc.t1"
 Column |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer           |           |          |         | plain    |             |              | 
 name   | character varying |           |          |         | extended |             |              | 
 addr   | character varying |           |          |         | extended |             |              | 
Access method: heap

4.6 匹配不同对象类型的\d命令,和模糊匹配搭配使用

假如只想模糊匹配表/索引/序列等,可以进行进一步限制

只想显示:	使用
表	\dt
索引	\di
序列	\ds
视图	\dv
函数	\df

4.7 显示SQL已执行的时间 \timing on 

db_test=# \timing on
Timing is on.
db_test=# select count(*) from dongsc.t1;
 count 
-------
     0
(1 row)

Time: 0.296 ms

4.8 列出当前数据库下所有schema   \dn

4.9 显示所有的表空间 \db

4.10 列出数据库中所有的角色或用户  \dg或du

\du和\dg命令等价,因为pg中用户和角色不区分

4.11 显示表的权限分配情况  \dp   或\z

db_test=# \z t1 
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 public | t1   | table |                   |                   | 
(1 row)

5 指定psql显示的字符集  \encoding 

就像oracle中的sqlplus依赖于NLS_LANG一样,psql也能够指定字符集,并且能够实时更改

postgres=# \encoding 
UTF8
postgres=# \encoding gbk
postgres=# \encoding 
GBK

6 设置输出的样式   \pset

postgres=# \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

只举简单的例子:

db_test=# \pset border 0
Border style is 0.
db_test=# select * from dongsc.t1;
id  name    addr  
-- ------- ------
 1 dongsc  jinan
 2 dongsc2 jinan2
(2 rows)

db_test=# \pset border 1
Border style is 1.
db_test=# select * from dongsc.t1;
 id |  name   |  addr  
----+---------+--------
  1 | dongsc  | jinan
  2 | dongsc2 | jinan2
(2 rows)

db_test=# \pset border 2
Border style is 2.
db_test=# select * from dongsc.t1;
+----+---------+--------+
| id |  name   |  addr  |
+----+---------+--------+
|  1 | dongsc  | jinan  |
|  2 | dongsc2 | jinan2 |
+----+---------+--------+
(2 rows)

7 将每列数据都拆分为单行显示

db_test=# \x
Expanded display is on.
db_test=# select * from dongsc.t1;
+-[ RECORD 1 ]---+
| id   | 1       |
| name | dongsc  |
| addr | jinan   |
+-[ RECORD 2 ]---+
| id   | 2       |
| name | dongsc2 |
| addr | jinan2  |
+------+---------+

这个功能和MySQL中的\G类似

8 执行在外部文件中的SQL语句或命令   

8.1 使用\i在psql内部执行

db_test=# \i test_dml.txt 
 id |  name   |  addr  
----+---------+--------
  1 | dongsc  | jinan
  2 | dongsc2 | jinan2
(2 rows)

8.2 使用-x在shell命令行中执行

[postgres@centos7 scripts]$ psql -d db_test -f test_dml.txt 
 id |  name   |  addr  
----+---------+--------
  1 | dongsc  | jinan
  2 | dongsc2 | jinan2
(2 rows)

9 显示信息 \echo 

postgres=# \echo 'hell'
hell
postgres=# \echo hell
hell

这个命令通常在.sql脚本文件中输出一些提示信息

加上''可以防止\等符号造成的转义

10 更多的命令 \?   --打开帮助文档

11. 开启与禁用自动提交

11.1 使用begin .... commit 或 begin...end 

postgres=# begin;
BEGIN
postgres=*# commit;
COMMIT

commit或end或rollback都会导致事务的结束

11.2 显式指定参数

postgres=# \echo :AUTOCOMMIT 
on
postgres=# \set AUTOCOMMIT off
postgres=# \echo :AUTOCOMMIT 
off

注意好像在新版本,变量和参数是不一样的,在一些教材上,使用set,但是实际实验是需要\set

12. 得到psql中实际的SQL

在启动psql时加-E参数,就可以把psql中各种以\为开头的命令执行的具体SQL输出来

[postgres@centos7 scripts]$ psql -E
psql (15.1)
Type "help" for help.

postgres=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

Did not find any relations.

-E参数和 :ECHO_HIDDEN参数是一样的

postgres=# \echo :ECHO_HIDDEN
on
postgres=# \set ECHO_HIDDEN off
postgres=# \d   --关闭ECHO_HIDDEN变量后,就不会再输出'\'具体的参数了
Did not find any relations.

就先这些,后续再更新