PostgreSQL用高機能ページャ pspg で表を見やすくする

こちらは PostgreSQL Advent Calendar 2022 の11日目です。
昨日は @abejjj さんの「prometheus&grafanaでPostgreSQLのレプリケーション遅延を可視化する。」でした。

はじめに

表データを見やすくするためのtips, ツールを紹介します！

• psqlでのページャlessのtips
• 高機能な表形式ページャ(ビューア)pspgの紹介

less

PostgreSQLクライアント psql ではページャとして環境変数PAGERの設定を見てくれるので、特に意識していなければ less を使っている方が多いのではないでしょうか。
PAGER設定がなければ、デフォルトで more または less が使われます。

折り返し制御

ありがちなのが、less を使っていると以下の用に selectクエリの結果のカラムが多い場合に折り返されて読みにくくなる、という事があると思います。

このlessの表示中に、- S と入力して Enterキーを押して見ましょう。

折り返し無しの状態では、矢印キー、または h lキーで左右にスクロールします。
デフォルトのスクロール幅を変えたい場合は 8lのように 数字を押した後に左右スクロールすると、以後はその文字数でスクロールします。

psqlでは折り返し無しをデフォルトにしたいのであれば、lessの起動オプション-Sを含めて設定しておくと良いかもしれません。
(その他のオプションはお好みで)

環境変数で指定しておいてからpsqlを起動

export PSQL_PAGER='less -FXS'
psql

または、~/.psqlrc でページャを設定します。

~/.psqlrc

\setenv PAGER 'less -FXS'

psqlの拡張表示(縦表示) \x を利用する場合も多いと思いますが、適宜使い分けてください。

表形式特化型高機能ページャ pspg

pspgは表形式に最適化されたページャです。
以下などの機能を持っています。

• 先頭の数行、または数列を固定表示
• 列名、または列番号でソート
• カラーテーマ
• マウス操作
• クリップボードにコピー

まずは作者の紹介動画でざっくり何ができるかご覧ください。

インストール

mac

brew install pspg

Debian

apt-cache search pspg
apt-get install pspg

サポート形式

もともと PostgreSQL psql用に設計されていますが、数種類の表形式のレイアウトに対応しているので、他の多くのデータベース(MySQL, SQLite, Oracleなど)も含めたCLIツールで使用することができ、CSV, TSV形式のような汎用フォーマットにも対応しています。

• 汎用フォーマット
  • csv
  • tsv
• 各種DB用CLIの表形式にレイアウトされた出力
  • psql
  • mysql
  • sqlite
  • pgcli
  • monetdb
  • Trino (formerly Presto SQL)
  • usql
  • sqlcl (for Oracle)
  • nushell

おすすめ設定

~/.psqlrc で pspg 用にページャを設定します。

~/.psqlrc

\setenv PAGER 'pspg -FX -s6 -c1 --only-for-tables --no-mouse --no-scrollbar --less-status-bar'

すると、以下のような見た目になります。
スクロールしてもヘッダのカラム名が固定表示されたままなので非常に見やすいです。

いくつかの機能について後述します。

キー操作

多くの操作が less と同じキーアサインとなっていますので、ほとんど違和感なく使えると思います。
参照: Keyboard commands

キー	操作
0, 1, 2, 3, .., 9	先頭N列を固定
KEY_UP, k	1行進む
KEY_DOWN, j	1行戻る
KEY_LEFT, h	左へスクロール
KEY_RIGHT, l	右へスクロール
Ctrl+KEY_LEFT	1文字左へスクロール
Ctrl+KEY_RIGHT	1文字右へスクロール
Shift+KEY_LEFT	1列左へスクロール
Shift+KEY_RIGHT	1列右へスクロール
Ctrl+Home, g	先頭行へ移動
Ctrl+End, G	最終行へ移動
Alt+l	指定行へ移動
H	ページ内の先頭行に移動
M	ページ内の真ん中の行に移動
L	ページ内の最終行に移動
PPAGE, Ctrl+b	1ページ戻る
NPAGE, Ctrl+f, space	1ページ進む
HOME, ^	先頭列に移動
END, $	最終列に移動
Ctrl+e	1行下へスクロール
Ctrl+y	1行下へスクロール
Ctrl+d	半ページ進む
Ctrl+u	半ページ戻る
s	ファイルに保存
/	順方向検索
?	逆方向に検索
n	次の検索マッチ
N	前の検索マッチ
c	列検索
Alt+/	選択範囲内を順方向に検索
Alt+?	選択範囲内を逆方向に検索
Alt+c	行カーソル表示をトグル
Alt+m	マウス取得をトグル
Alt+n	行番号表示をトグル
Alt+v, double click on column header	列カーソル表示をトグル
Mouse button wheel	垂直スクロール
Alt+Mouse button wheel	水平スクロール
F9	メニュー表示
q, F10, Esc 0	終了
Alt+q	終了＆生データ表示
Alt+k, Alt+double click	ブックマークをトグル
Alt+j	次のブックマークへ移動
Alt+i	前のブックマークへ移動
Alt+o	ブックマークをクリア
a	昇順ソート
d	降順ソート
u	ソート解除 (元の並びに戻す)
Space	監視モードで停止/再開
R	画面再描画＆入力ファイルをリフレッシュ
Ins	行、列、またはセルをエクスポート(ファイル or クリップボード)
shift+cursor...	範囲選択
F3	行選択の開始/終了
Shift+F3	ブロック選択の開始/終了
Ctrl+drag mouse	行選択、(ヘッダ上では)列選択
Ctrl+o	メイン画面を表示、任意のキー押下でページャに戻る

(注意) lessと異なる気をつけたいキー操作

lessとキーアサインが異なり、少し戸惑う操作があります。

操作	less	pspg
数値	(数値+gで)指定行に移動	先頭列を固定する
f	次ページへ	なし (代わりにSPACE or Ctrl-Fを使おう)
b	前ページへ	なし (代わりにCtrl-Bを使おう)

バックスラッシュ コマンド

\キーから始まるバックスラッシュ コマンドはタブによる入力補完が効きます。
参照: Backslash commands

コマンド	詳細
\N	指定行番号へ移動
\+N	N行進む
\-N	N行戻る
\N+	指定行番号へ移動
\N-	末尾からN行目へ移動
\theme N	テーマ番号Nに切り替え
\copy [all|selected] [nullstr "str"] [csv|tsv|insert|text|pipesep|sqlvalues]	クリップボードにコピー
\save [all|selected] [nullstr "str"] [csv|tsv|insert|text|pipesep|sqlvalues]	クリップボードにコピー
\order [N|colum name]	列を昇順ソート
\orderd [N|colum name]	列を降順ソート
\sort [N|colum name]	列を昇順ソート
\sortd [N|colum name]	列を降順ソート
\dsort [N|colum name]	列を降順ソート (alias)
\rsort [N|colum name]	列を降順ソート (alias)
\asc [N|colum name]	列を昇順ソート (alias)
\desc [N|colum name]	列を降順ソート(alias)
\search [back] [selected] [colum name] [string|"string"]	文字列で検索

カラーテーマ (オプション: -s, --style=N)

カラーテーマはたくさん用意されており、カスタマイズも可能ですが、
私はダークモードで使っているので一番違和感のない6番(PCFand like)を使っています。

列固定 (オプション: -c)

先頭のN列目を固定表示とし、左右スクロールしたときにidなどの重要な情報が画面に残り見易いです。
ページャ表示中に 0 1 2 など数字を入力すると、固定表示を変更できます。

※↓は２列目までを固定化した例

less風ステータス (オプション: --less-status-bar)

pspgのデフォルトだと画面の最上部にステータス表示されるのですが、

これを、less風の表記でlessと同様に左下にステータス表示します。

less と pspg を併用する場合は、視線がステータスを探すときに右上？左下？と混同しなくて良いのでおすすめです。

指定カラムへジャンプ (cキー)

検索 (/, n, Nキー)

このあたりも less と同様に使えます

• / + キーワード: キーワードで検索
• n: 次を検索
• N: 前を検索

ソート (a, d, uキー / \sortコマンド)

いくつかの方法でソートできます。
ただし、(selectクエリとしてのソートではなく) あくまでもページャ上でのソートなので誤解されないように。

• 現在のカラム位置でソートキー
  • aキー: 昇順(asc)
  • dキー: 降順(desc)
  • uキー: ソート解除
• ソートコマンドでカラム指定
  • \sort 列番号 または \sort カラム名: 昇順
    • 別名(\order, \asc, \asc)
  • \sortd 列番号 または \sortd カラム名: 昇順
    • 別名(\orderd, \dsort, \rsort, \desc, \asc)

クリップボードにコピー (\copyコマンド)

全体、範囲選択、検索マッチ行 などに対して、行データをクリップボードにコピーすることができます。
コピー書式は CSV形式, TSV形式, Insert文など複数サポートとしています。
なお、プラットフォームごとに xclip や pbcopy などのクリップボード連携コマンドを自動識別してくれます。

以下の書式でコピーの範囲、コピー形式等を制御します。

コピーコマンド書式

\copy [all|selected|searched] [nullstr "str"] [csv|tsv|insert|text|pipesep|sqlvalues]

ここもタブで入力補完が効くので意外に楽に使えます。

また、pspg はデフォルトで unicode文字 ∅ をヌルを意味する文字列とみなして内部的に NULL に変換し、出力書式によって良しなに処理してくれます。
この文字列指定は pspg の起動オプション --null=STRING でも指定が可能です。

psql側での null表示用文字列と合わせておくと便利でしょう。

~/.psqlrc

\pset null ∅

less と pspg の使い分け

githubページに紹介があるように、less と pspg を使い分けるためのpsql変数を設定しておくと便利です。

~/.psqlrc

-- 拡張表示を自動にしてページャをlessに切り替え
\set x '\\x auto \\setenv PAGER \'less -S\''

-- 拡張表示を無効にしてページャをpspgに切り替え
\set xx '\\x off \\setenv PAGER \'pspg -FX -s6 -c1 --only-for-tables --no-mouse --no-scrollbar --less-status-bar\''

使うときは、:x や :xx として切り替えます。

-- ページャをpspgに切り替える
# :xx
# select * from foo;

(注意) ストリーム読み込みの特性

pspg はストリームを(非同期ですが)すべて読み込もうとします。
そのため、レコード数の多いテーブルを気軽に select * from large_table; みたいな感じで読もうとすると大量のfetchが発生して、サーバやネットワークの負荷的に宜しくありません。
基本的には開発環境での使用、または補助的な活用が良いと思います。

ささやかな対策として、以下のような上限を設定しておく、という方法があります。

読み込み上限設定

-- 上限を10002行(=先頭1万行+ヘッダ分2行)とする
\setenv PAGER 'head -10002 | pspg'

その他の機能

• メニュー システム
• スクロールバー
• マウスサポート

pspg はマウスをサポートしているので、(ターミナル側が適切に設定されていれば)マウスで操作が可能です。
選択行や検索にマッチした行を、CSV, TSV, SQL Insert文 などの書式でクリップボードにコピーすることができます。

しかし、ページャとして実現できる仕組みには限りがあるので、ちょっと凝った操作をしたい場合は別のクライアントツール(IDEのDB機能やTablePlusなどのGUIツール)を使うのが良いと思います。

参考URL

• okbob/pspg (github)
• psql (PostgreSQL14.5日本語ドキュメント)

---

明日は @hmatsu47 さんの「Supabase で TCE（透過的列暗号化）をアプリケーションから使ってみた」です。