psql元命令之简单爱

多米爸比@云和恩墨

于 2024-04-07 08:12:45 发布

阅读量862

点赞数 17

文章标签：数据库 postgresql

本文链接：https://blog.csdn.net/jerome_peng/article/details/137424235

版权

本文整理psql用到的一些元命令及搭配的工具，可提高日常的工作效率。

快速编辑SQL并执行
定制客户端提示符
使用边框美化查询结果
独立存储每个DB的历史SQL
输出不同的查询格式
持续监控运行中的语句
获取结果集的元数据
动态执行查询语句
条件表达式精简代码
高效搭配tmux分屏工具
定位错误源码位置

快速编辑SQL并执行

元命令\e自动打开上次的查询进行编辑，编辑完成之后立刻执行

上面使用\e直接在上一个SQL语句里进行编辑修改，然后wq保存退出编辑模式之后，自动执行新SQL语句。

定制psql客户端提示符

psql的客户端提示符变量，我们可以定制PROMPT1(等待新命令时的提示符)和PROMPT2(命令未输入完成时的提示符)，例如.psqlrc文件可以进行下面的设置：

\set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x'
\set PROMPT2 '%w'

PROMPT1变量可以定制操作系统的命令，例如显示时间。PROMPT2变量使用%w可以生成与语句对齐的空格，select语句的第二行和第三行可读性更强，对于复杂语句可以更加方便进行复制粘贴。

使用边框美化查询结果

在.psqlrc文件增加下面的设置，可以对查询结果设置边框，美化输出。

\pset border 2
\pset linestyle unicode

左边是默认不带边框的输出，右边则是增加了上面两行设置的边框输出。

另外如果字段值比较长，可以在psql里面使用如下pset命令

\pset format wrapped
\pset columns 0

或者psql选项里加这两个设置

$ psql --pset=format=wrapped --pset=columns=0

企业微信截图_fa3847fc9b204e4397450a92589fe7a8.png

独立存储每个DB的历史SQL

在.psqlrc文件里可以设置HISTFILE变量，利用内置的DBNAME对不同的database使用不同的histfile文件进行历史SQL记录。

\set HISTFILE ~/.psql_history_:DBNAME

进行上面设置后，不同的database下的操作语句会记录到不同的文件。注意在psql里使用元命令\c进行的切库操作不会生效。

输出不同的查询格式

文本紧凑模式

$ psql -qtA  -c command

文本紧凑模式，使用多字符分隔符@@

$ psql -qtA -F '@@'  -c  command

HTML格式

$ psql -qtH  -c command

CSV格式，逗号分隔

$ psql -qt --csv  -c command

持续监控运行中的语句

一些比较费时的后台操作进度报告视图，例如pg_stat_progress_copy，可以使用\watch元命令每隔几秒进行观察输出。

select datname,relid::regclass as table, command,type, bytes_processed,tuples_processed from pg_stat_progress_copy \watch 1

例如下面监控索引进度：

从16版本开始，\watch还支持运行指定的次数，语法如下：

\watch [[i=]SEC] [c=N] execute query every SEC seconds, up to N times

下面是运行7次，每隔2秒运行一次。

\watch i=2 c=7

获取结果集的元数据

SQL Server数据库可以使用set fmtonly on获取结果集的元数据信息，在PG里可以使用元命令\gdesc来获取。

postgres=# select relname,reloptions from pg_class \gdesc
   Column   |  Type  
------------+--------
 relname    | name
 reloptions | text[]
(2 rows)

动态执行查询语句

当我们使用查询去拼接SQL，再拷贝生成的语句去执行时，可以使用元命令\gexec直接执行查询结果。

postgres=# select 'drop user if exists testuser; create user testuser password '|| quote_literal(repeat('a',1024))||';' \gexec
CREATE ROLE

例如上面的操作拼接了两条SQL，然后直接执行。

条件表达式精简代码

使用元命令\if \else条件表达式可以让业务逻辑变得更清晰，参考如下：

$ psql  <<EOF
    select pg_is_in_recovery() as in_recovery \gset
    \if :in_recovery
        \echo -n 'do nothing ...'
    \else
        --call procedure or do something;
    \endif
EOF