推荐Postgresql中一些好用的psql命令（2）
psql客户端工具应该是dba非常频繁使用的的工具。我想把自己觉得有助于提升工作效率的psql的命令整理一下，这些命令可以让你更好的使用postgresql。如果你是一个小白，那么这篇文章一定会让你有所收益。
我们接着上一篇文章，继续给大家介绍几个好用的psql命令。
回写\d等反斜杠命令执行的真实SQL
回写\d等反斜杠命令执行的真实SQL，这样方便我们了解其内部查询的逻辑，比如是使用那个schema下的系统表获取的数据等等。我们有两种方式可以操作，一种是在psql后面添加-E或–echo-hidden参数;另外一种是\set ECHO_HIDDEN变量 。
E或–echo-hidden参数
[postgres@wang ~]$ psql -E
psql (16.3)
Type "help" for help.
postgres=#
\set ECHO_HIDDEN变量
postgres=# \set ECHO_HIDDEN on
执行\l命令
通过上面的两种方式设置，然后执行\l命令后，我们在界面上就看到了\l命令在数据库上实际执行的sql语句，两种方式的结果是一样的。
postgres=# \l
********* QUERY **********
SELECT
d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
d.datcollate as "Collate",
d.datctype as "Ctype",
d.daticulocale as "ICU Locale",
d.daticurules as "ICU Rules",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
demo | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
testdb | test | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =Tc/test +
| | | | | | | | test=CTc/test
(5 rows)
输出执行结果及其SQL语句
当我们批量执行文件中SQL语句时，想要查看执行的结果详情，通常通过标准输出文件，只有sql的执行结果，没有执行SQL，很不方便我们确认执行结果，就如下面执行的结果。
psql -f test.sql
[postgres@wang ~]$ cat pg0927.log
id | name | address
----+--------+---------
1 | wang1 | beijing
2 | wang2 | beijing
4 | wang4 | beijing
3 | wang3 | beijing
5 | zhaosi |
6 | lisan |
7 | lisi | aaa
(7 rows)
UPDATE 1
id | name | address
----+-------+---------
1 | wang1 | beijing
2 | wang2 | beijing
4 | wang4 | beijing
(3 rows)
可以通过下面的两种方式把SQL和执行结果都输出到一个文件。
使用-L命令
使用-L命令后，会把SQL执行结果及SQL语句都输出到指定文件中，对应输出的文件，还进行了格式化，更方便用户核实执行结果。
psql -f test.sql -L pg09273.log
[postgres@wang ~]$ cat pg09273.log
********* QUERY **********
select * from t1;
**************************
id | name | address
----+--------+---------
1 | wang1 | beijing
2 | wang2 | beijing
4 | wang4 | beijing
3 | wang3 | beijing
5 | zhaosi |
6 | lisan |
7 | lisi | aaa
(7 rows)
********* QUERY **********
update t1 set address = 'aaa' where id = 7;
**************************
UPDATE 1
********* QUERY **********
select * from t1 limit 3;
**************************
id | name | address
----+-------+---------
1 | wang1 | beijing
2 | wang2 | beijing
4 | wang4 | beijing
(3 rows)
使用-e命令
利用-e命令把执行的SQL复制到标准输出并结合默认查询结果，也可以满足上面的要求。
psql -f test.sql -e >> pg09274.log
[postgres@wang ~]$ cat pg09274.log
You are now connected to database "testdb" as user "postgres".
select * from t1;
id | name | address
----+--------+---------
1 | wang1 | beijing
2 | wang2 | beijing
4 | wang4 | beijing
3 | wang3 | beijing
5 | zhaosi |
6 | lisan |
7 | lisi | aaa
(7 rows)
update t1 set address = 'aaa' where id = 7;
UPDATE 1
select * from t1 limit 3;
id | name | address
----+-------+---------
1 | wang1 | beijing
2 | wang2 | beijing
4 | wang4 | beijing
(3 rows)
自定义提示符
psql的默认提示符是数据库名称，也没有显示其他内容。那么，是否可以自定义设置呢，其实这个是可以满足实现的，可以添加一些主机和会话的信息。我自己习惯添加日期和时间，这样可以随时知道执行sql的当时时间；另外如果你保存会话的信息，也方便查看历史。
\set PROMPT1 '%/ %`date "+%Y-%m-%d %H:%M:%S"`=#'
设置完成的效果如下
testdb=# \set PROMPT1 '%/ %`date "+%Y-%m-%d %H:%M:%S"`=#'
testdb 2024-09-27 17:02:41=#
testdb 2024-09-27 17:02:43=#
自定义快捷方式
使用自定义快捷方式，可以很方便的我们手动快速执行一些常用SQL语句。比如查看表大小、未使用索引、执行耗时长的查询等等。下面通过自定义设置查询表大小快捷方式。
--查询表大小
\set table_sizes 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (''pg_catalog'', ''information_schema'') AND n.nspname !~ ''^pg_toast'' AND c.relkind=''r'' ORDER BY pg_table_size(c.oid) DESC;'
在设置查询表大小后，然后在psql中可以使用冒号和查询的名称来运行它，这样就可以使用一个超级简单的psql输入来运行它们。
testdb=# :table_sizes
name | size
-------------------+------------
bmsql_stock | 3397 MB
bmsql_order_line | 2895 MB
bmsql_customer | 1772 MB
bmsql_history | 253 MB
bmsql_oorder_test | 189 MB
bmsql_oorder | 189 MB
bmsql_new_order | 38 MB
bmsql_item | 10 MB
bmsql_district | 200 kB
bmsql_warehouse | 56 kB
bmsql_config | 40 kB
设置默认的.psqlrc配置文件
上面的设置，每次使用时都需要设置。那么，是否有方便的办法呢。其实是有的，对于上面的所有设置都可以在每次使用psql时自动生效。在这里，就需要提到.psqlrc，当psql启动时，它会自动查找.psqlrc文件，如果存在，将执行其中的命令。这就方便我们自定义提示和查询快捷方式及其他psql设置。
先来查看一下是否存在.psqlrc文件
ls -la ~/.psqlrc
如果不存在，我们可以touch创建一个文件或者直接vim编辑该文件，并添加上述自己需要的设置命令
touch ~/.psqlrc
或
vim ~/.psqlrc
在.psqlrc中添加如下设置
\set PROMPT1 '%/ %`date "+%Y-%m-%d %H:%M:%S"`=#'
\set table_sizes 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (''pg_catalog'', ''information_schema'') AND n.nspname !~ ''^pg_toast'' AND c.relkind=''r'' ORDER BY pg_table_size(c.oid) DESC;'
当我们再次进入psql时，上述的设置就生效了。
[postgres@wang ~]$ psql
psql (16.3)
Type "help" for help.
postgres 2024-10-07 15:48:01=#\c testdb
You are now connected to database "testdb" as user "postgres".
testdb 2024-10-07 15:48:07=#
testdb 2024-10-07 15:48:08=#
testdb 2024-10-07 15:48:08=#:table_sizes
name | size
-------------------+------------
bmsql_stock | 3397 MB
bmsql_order_line | 2895 MB
bmsql_customer | 1772 MB
bmsql_history | 253 MB
bmsql_oorder_test | 189 MB
快速搜索历史
你是否记得我们经常查询shell命令的执行历史，那么对于psql的执行历史，是否也可以快速查找，答案肯定是可以的，要不我就没必要说了。可以通过ctrl+R命令来快速查询执行的历史命令。
总结
上面的这些psql命令的使用方式，我希望能够对你的工作有所启发。
– / END / –
可以通过下面的方式联系我
如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！
