注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
psql 的使用技巧：设置、预设、回显和已保存的查询
文章快速说明索引
学习目标：
做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。
学习内容：（详见目录）
1、psql 的使用技巧：设置、预设、回显和已保存的查询
学习时间：
2024年07月23日 20:19:05
学习产出：
1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习
注：下面我们所有的学习环境是Centos8+PostgreSQL master +Oracle19C+MySQL8.0
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 18devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
BANNER_FULL Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
CON_ID 0
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.06 sec)
mysql>
功能使用背景说明
在我使用 Postgres psql cli 的过程中，我从 Crunchy Data 的同事那里学到了一些好习惯，这些习惯让我的终端数据库环境更易于使用。我想分享一些我发现的我最喜欢的东西，它们可以让 Postgres 变得更好。如果您刚刚开始使用 psql，或者还没有尝试太多默认设置，那么这篇文章就是为您准备的。我将带您了解一些最友好的 psql 设置以及如何创建您自己的预设设置文件。
一些最有用的 psql 命令
格式化 psql 输出
Postgres 具有扩展显示模式，它将以列和数据批次的形式读取查询结果，而不是以向右扩展显示的大量列列表的形式读取。
扩展显示示例如下所示：
-[ RECORD 1 ]------------------------------
id | 1
name | Alice Johnson
position | Manager
department | Sales
salary | 75000.00
-[ RECORD 2 ]------------------------------
id | 2
name | Bob Smith
position | Developer
department | Engineering
salary | 65000.00
--Automatically format expanded display for wide columns
\x auto
如果您刚刚开始并想尝试这些命令，我有一个关于使用基本 psql 的教程。
psql 输出中的表格列边框
如果您不使用扩展显示，您可以让 psql 使用 \pset linestyle来制作一些精美的列轮廓。
--Outline table borders and separators using Unicode characters
\pset linestyle unicode
这将获得如下查询输出：
[postgres@localhost:~/test/bin]$ ./psql
psql (18devel)
Type "help" for help.
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
xheader_width full
postgres=#
postgres=# select * from t1;
id | name
----+------------
1 | Oracle
2 | MySQL
3 | PostgreSQL
(3 rows)
postgres=# \pset border 2
Border style is 2.
postgres=# select * from t1;
+----+------------+
| id | name |
+----+------------+
| 1 | Oracle |
| 2 | MySQL |
| 3 | PostgreSQL |
+----+------------+
(3 rows)
postgres=# \pset linestyle unicode
Line style is unicode.
postgres=# select * from t1;
┌────┬────────────┐
│ id │ name │
├────┼────────────┤
│ 1 │ Oracle │
│ 2 │ MySQL │
│ 3 │ PostgreSQL │
└────┴────────────┘
(3 rows)
postgres=#
在 psql 中显示查询运行时间
这将在底部为您提供查询运行时间（以毫秒为单位）的结果：
-- Always show query time
\timing
在 psql 中为空值创建预设
这将适用于表情符号或任何兼容 utf-8 的内容：
-- Set Null char output to differentiate it from empty string
\pset null '☘️'
postgres=# select null;
┌──────────┐
│ ?column? │
├──────────┤
│ │
└──────────┘
(1 row)
postgres=# \pset null '☘️'
Null display is "☘️".
postgres=#
postgres=# select null;
┌──────────┐
│ ?column? │
├──────────┤
│ ☘️ │
└──────────┘
(1 row)
postgres=#
您的 psql 历史记录
您可以为您的 psql 命令会话创建历史记录文件，如下所示：
-- Creates a history file for each database in your config directory CHECK IF THIS IS RIGHT
\set HISTFILE ~/.config/psql/psql_history-:DBNAME
-- Number of commands to save in history
\set HISTSIZE 2000
若是没有设置，如下：
[postgres@localhost:~]$ find . -name *psql_history*
./.psql_history
[postgres@localhost:~]$
[postgres@localhost:~]$ head -n 5 ./.psql_history
create table t2 (id int,id2 posint);
select create_distributed_table('t2', 'id');
\d
drop domain posint cascade;
drop table t1;
[postgres@localhost:~]$
将 PSQL 命令回显为 SQL
任何 psql 斜线命令（例如 \d）都会针对 Postgres 的系统表运行。您可以使用 psql echo 命令显示给定命令使用的查询，这可以让您深入了解 Postgres 的内部表、目录和其他命名约定。
-- output any SQL run by psql slash commands
\set ECHO_HIDDEN on
-- short name of ECHO_HIDDEN on
-E
现在让 echo 显示一些东西。使用以下命令进行表查找：
\dt+
现在您将看到它向您回显了用于获取该数据的查询，并且在底部显示了 \dt+ 的正常结果：
[postgres@localhost:~/test/bin]$ ./psql -E
psql (18devel)
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",
CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
am.amname as "Access method",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
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;
/************************/
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
public | t1 | table | postgres | permanent | heap | 16 kB |
(1 row)
postgres=#
这块功能的源码解析可以查看本人之前的博客：
回显所有 Postgres psql 查询
您还可以让 psql 回显其运行的所有查询：
-- Have psql echo back queries
\set ECHO queries
-- Short name of echo queries
-e
[postgres@localhost:~/test/bin]$ ./psql -e
psql (18devel)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
postgres=# select * from t1;
select * from t1;
id | name
----+------------
1 | Oracle
2 | MySQL
3 | PostgreSQL
(3 rows)
postgres=# \q
[postgres@localhost:~/test/bin]$ ./psql -e -E
psql (18devel)
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;
/************************/
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
postgres=# select * from t1;
select * from t1;
id | name
----+------------
1 | Oracle
2 | MySQL
3 | PostgreSQL
(3 rows)
postgres=#
如果您正在从文件运行查询，或者它们是 psqlrc 中的预设，并且希望查询第二次输出以进行记录保存，那么这将非常有用。
如果您想进一步了解其中任何一个，我有一个关于 ECHO HIDDEN 和 ECHO 查询的基于网络的教程。
使用 .psqlrc 设置您的默认 psql 体验
我列出的所有上述事项都可以设置为每次使用本地 psql 时自动发生。当 psql 启动时，它会查找 .psqlrc 文件，如果存在，它将执行其中的命令。这允许您自定义提示和其他 psql 设置。
您可以使用以下命令查看您是否有 .psqlrc 文件：
ls -l ~/.psqlrc
如果您想在启动 psql 时跳过命令记录，您可以将这些命令添加到文件的开头和结尾：
-- Don't log these commands at the beginning of the file
\set QUIET 1
-- Reset command logging at the end of the file
\set QUIET 0
自定义提示行
psql 的默认提示行只显示数据库名称，没有其他信息。在 psqlrc 文件中，您可以更改 psql 提示行以使用有关数据库主机和会话的不同信息组合。我个人喜欢在这里使用日期和时间，因为我要保存会话以供以后参考。
-- Create a prompt with host, database name, date, and time
\set PROMPT1 '%m@%/ %`date "+%Y-%m-%d %H:%M:%S"` '
或者，如下：
[postgres@localhost:~/test/bin]$ cat ~/.psqlrc
\set PROMPT1 '%m@%n-%/-%> %`date "+%Y-%m-%d %H:%M:%S"` =%# '
[postgres@localhost:~/test/bin]$
[postgres@localhost:~/test/bin]$ ./psql
psql (18devel)
Type "help" for help.
[local]@postgres-postgres-5432 2024-07-24 00:22:26 =# select * from t1;
id | name
----+------------
1 | Oracle
2 | MySQL
3 | PostgreSQL
(3 rows)
[local]@postgres-postgres-5432 2024-07-24 00:22:33 =# exit
[postgres@localhost:~/test/bin]$
// src/bin/psql/prompt.c
/*--------------------------
* get_prompt
*
* Returns a statically allocated prompt made by interpolating certain
* tcsh style escape sequences into pset.vars "PROMPT1|2|3".
* (might not be completely multibyte safe)
*
* Defined interpolations are:
* %M - database server "hostname.domainname", "[local]" for AF_UNIX
* sockets, "[local:/dir/name]" if not default
* %m - like %M, but hostname only (before first dot), or always "[local]"
* %p - backend pid
* %> - database server port number
* %n - database user name
* %/ - current database
* %~ - like %/ but "~" when database name equals user name
* %w - whitespace of the same width as the most recent output of PROMPT1
* %# - "#" if superuser, ">" otherwise
* %R - in prompt1 normally =, or ^ if single line mode,
* or a ! if session is not connected to a database;
* in prompt2 -, *, ', or ";
* in prompt3 nothing
* %x - transaction status: empty, *, !, ? (unknown or no connection)
* %l - The line number inside the current statement, starting from 1.
* %? - the error code of the last query (not yet implemented)
* %% - a percent sign
*
* %[0-9] - the character with the given decimal code
* %0[0-7] - the character with the given octal code
* %0x[0-9A-Fa-f] - the character with the given hexadecimal code
*
* %`command` - The result of executing command in /bin/sh with trailing
* newline stripped.
* %:name: - The value of the psql variable 'name'
* (those will not be rescanned for more escape sequences!)
*
* %[ ... %] - tell readline that the contained text is invisible
*
* If the application-wide prompts become NULL somehow, the returned string
* will be empty (not NULL!).
*--------------------------
*/
char *
get_prompt(promptStatus_t status, ConditionalStack cstack);
保存在 psqlrc 文件中的查询
这个 .psqlrc 文件看起来很酷，对吧？但等等……还有更多！您可以将查询添加到此文件，以便只需使用非常简单的 psql 输入即可运行它们。
将这些示例查询添加到 psqlrc，用于长时间运行的查询、缓存命中率、unused_indexes 和表大小。
\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > interval ''5 minutes'' ORDER by 2 DESC;'
\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''table hit rate'' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'
\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
\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;'
案例，如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (18devel)
Type "help" for help.
[local]@postgres-postgres-5432 2024-07-24 00:28:24 =# :table_sizes;
name | size
------+-------
t1 | 16 kB
(1 row)
[local]@postgres-postgres-5432 2024-07-24 00:28:27 =#
尝试使用 psql 环境
我们希望这些内容能给您一些关于尝试使用 psql 环境的想法。这非常简单而且很有趣！成功秘诀：
- 优先帮助自己处理每天使用但需要花费时间的事情。您是否每周在数据库上运行一次查询？将其放入 psqlrc 文件中，以便下次使用时可以立即找到。
- 如果您远程连接到数据库，请不要太过分。如果您不使用本地连接到数据库并直接远程连接，请不要创建大量特殊工具，因为使用不同的环境可能会很麻烦。
- 查看我们的基本 psql 和 ECHO HIDDEN 和 ECHO 查询教程，以便在 Web 浏览器中尝试这些内容
我们的 Postgres 提示页面中还有大量其他方便的 psql 技巧。