AppBrew Tech Blog

株式会社AppBrewの技術に関するブログ

【普段使いできる】psql メタコマンド10選【🐘🐘】

AppBrew で LIPS のリードエンジニアをしている @spinute です。

突然ですが、みなさん、コマンドラインは好きですか？

私は好きです。好きなコマンドは tldr, glances, tig で、よく使うコマンド Top-10 はこんな感じです。1

$ history | awk '{print $2}' | sort | uniq -c | sort -nr | awk 'NR<=10 {print $2}' | cat -n
   1 git
   2 vi
   3 fg
   4 tig
   5 l
   6 cd
   7 grep
   8 curl
   9 aws
  10 go

ストイック・ハードボイルド路線ではなく、シンプルで安定したインターフェースを提供しながらも、認知上の負荷を下げてくれるリッチなコマンドが好きです。

AppBrew では、ウェブ生まれアプリ育ちの人が多めだったり、ソフトウェアエンジニアの平均年齢やや低め2だったりするためか、GUI ユーザが比較的多いイメージです。3

この記事では、社内向けの布教も兼ねて、普段使いに便利な psql のメタコマンドを紹介します。

psql とは

psql は PostgreSQL 付属の CUI クライアントです。

弊社サービス LIPS のマスターデータは MySQL に入っていますが、データ分析用のために Redshift も併用しています。 Redshift は PostgreSQL とある程度の互換性があり、psql を使って Redshift に SQL クエリを発行できます。

さて本題ですが、psql では、SQL の機能だけでなく、メタコマンドと呼ばれる独自の便利コマンドを使えることはご存知でしょうか。

メタコマンドは \ からはじまるコマンドで、公式ドキュメントに網羅的な説明があります。

この記事では、数多くあるメタコマンドの中でも、このへん把握しとけばとりあえず十分だというものを紹介します。4

psql メタコマンド10選

\set：変数の設定

メタコマンド \set を使うと変数に値を設定できます。（変数の意味はここでは説明しません。興味がある方はマニュアルを参照してください。）

試しに、この記事のサンプルプログラムでは、プロンプトを => にします。

/* ~/.psqlrc（psql 起動時に読み込むファイル） */
\set PROMPT1 '%[%033[01;33m%]=>%033[0m%] '
\set PROMPT2 '%[%033[01;33m%]=>%033[0m%] '
\set PROMPT3 '%[%033[01;33m%]=>%033[0m%] '

\timing：問い合わせ実行時間の表示

メタコマンド \timing を実行すると、問い合わせにかかった時間が表示されるようになります。

小物なんですが地味に便利です。私はこれも .psqlrc に書いています。

\?：メタコマンドのヘルプ

まずは慣習に従ってヘルプから。

=> \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
...

\h を実行すると、メタコマンドではなく SQL のヘルプが出てくるのでご注意ください（これはこれで便利です）。

=> \h
Available help:
  ABORT                            ALTER ROLE                       CHECKPOINT                       CREATE OPERATOR                  CREATE USER                      DROP MATERIALIZED VIEW           DROP TRIGGER                     RESET
  ALTER AGGREGATE                  ALTER ROUTINE                    CLOSE                            CREATE OPERATOR CLASS            CREATE USER MAPPING              DROP OPERATOR                    DROP TYPE                        REVOKE
  ...

help; と打ってる迷ってそうな人にはそのへんの使い分けを教えてくれます。親切ですね。

=> help;
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

\e：エディタ上でクエリを編集

psql をインタラクティブな書き捨てクエリ用ツールと割り切っている人は多い気がします。 とはいえ、BI ツールのエディタは手に馴染まない5し、いつものエディタでクエリを書いて BI ツールに貼り付けるのは面倒だし、どうしたもんじゃいと思っている人がいるのではないでしょうか？

モダンな（？）インタプリタにはよくある機能ですが、psql でも、メタコマンド \e でエディタを起動し、クエリバッファを開き、その上でクエリを作成・編集できます。 また、ファイルを編集し終えると、その結果を読み込んでクエリとして送信します。

# エディタ上が起動するので、ここでは select 123; と書き込む
=> \e
 ?column?
----------
      123
(1 row)

さらに、メタコマンド \w <filename> を実行すれば、先ほど編集したクエリバッファの内容をファイルに書き込むこともできます。

=> \w 123.txt

$ cat 123.txt
select 123;

つまり、エディタでクエリを編集、実行し、気に入った結果が得られるまでそれを繰り返し、最後にファイルに保存するワークフローを psql 上で終えられます。

\i：ファイルを読み込んで処理を実行

メタコマンド \i <filename> を使って、ファイルから処理を読み込むこともできます。

=> \i 123.txt
 ?column?
----------
      123
(1 row)

\o：問い合わせ結果をファイルに出力

メタコマンド \o <filename> を使えば、それ以降の問い合わせ結果をファイルに書き出せます。

また、\g <filename> を実行すると、直前に実行したクエリの結果を指定したファイルに書き込めます。6

\H：HTML として出力

\H を実行すると、以降の出力が HTML 形式になります。

Qiita や Github 等にクエリの結果を貼るときに便利です。

=> \H
Output format is html.
=> select 123 as one, 456 as two;
<table border="1">
  <tr>
    <th align="center">one</th>
    <th align="center">two</th>
  </tr>
  <tr valign="top">
    <td align="right">123</td>
    <td align="right">456</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

先程紹介した \g と組み合わせて使うことも多いです。

=> \g out.html

$ cat out.html
<table border="1">
  <tr>
    <th align="center">one</th>
    <th align="center">two</th>
  </tr>
  <tr valign="top">
    <td align="right">123</td>
    <td align="right">456</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

\pset：その他色々な出力形式設定

メタコマンド \pset を使うと、出力の形式を柔軟に指定できます。

できることは色々あるのですが、とりあえず format 項目だけ覚えておけば一般的なフォーマットでの整形を済ませられます：

  • CSV で出力：\pset format csv
  • LaTeX だっていける：\pset format latex
  • AsciiDoc だっていけちゃいます：\pset format a（一意に決まれば省略できるため a と書いています）

一点、実用上需要多そうな Markdown 出力機能がないんですよね...。

このへんでやろうとしてた人がいたようですが、パッチ投げるところまで至っていないようです...。

AsciiDoc はこんな感じで入れたようで、Markdown 単純だし雰囲気真似ればわりと簡単かも...？7と思ったところで放置しています👶

実際 Markdown の中で HTML を書けることも多く、その場合は先述した \H で済むので、実はそんなに需要がない...？

\d：情報取得

おそらく describe の頭文字でしょうか？様々な DB 内オブジェクトの情報を取得できます。ググるより手早くて便利です。

\d からはじまるメタコマンドが大量にあるので、詳しくは \? を見てください。

\dt を使うとテーブルの情報を見られます。テーブル名が曖昧なときは、ワイルドカード * も使えます。8

=> \dt user*
                         List of relations
 schema |                   name                    | type  | owner
--------+-------------------------------------------+-------+-------
 schema | user_profiles                             | table | pin
 schema | user_relations                            | table | pin
 schema | users                                     | table | pin

\dを使えば、個々のテーブルのスキーマを確認できます。9

=> \d user_relations
                        Table "schema.user_relations"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 id           | integer                     |           | not null |
 following_id | integer                     |           |          |
 follower_id  | integer                     |           |          |

余談：Redshift で \daS を実行すると hll というドキュメントにない関数が見えたのでオッ！と思ったんですが、実行できず...。approximate count の内部実装だけで使ってる感じなんすかね、無念...。

\crosstabview：クロス集計

実はちょっとした集計機能も psql には入っています。\crosstabview を使うと、クエリの実行結果をクロス集計できます。10

試しに、自己紹介文の長さと、投稿文の長さの関係をクロス集計してみましょう。

集計の軸を表す2つのカラムと、結果を表す1つのカラム、合わせて3つのカラムからなるテーブルを出力するクエリを書けばよいです。

=> select trunc(log(greatest(char_length(bio), 1))) as bio_length,
=> trunc(log(greatest(char_length(content), 1))) as content_length,
=> count(*)
=> from posts join users on user_id = users.id
=> group by 1, 2
=> order by 1, 2
=> \crosstabview
 bio_length |   0    |   1    |    2    |   3
------------+--------+--------+---------+-------
          0 | 100000 | 200000 |  200000 |  1000
          1 |  30000 | 200000 | 1000000 | 50000
          2 |   1000 |   5000 |   10000 | 10000

なんとなく相関ありそうですね。11

おわりに

今日は psql の推しメタコマンド10選をテーマに記事を書きました📍

LIPS には1ミリも関係ないと見せかけて、先日 Redshift の RA3 インスタンス乗せ替え作業をやったときに psql をいつもより多く触った勢いで書いたものです📌

AppBrew では引き続き、コマンドラインが好きな人、プロダクト作りが好きな人、toC サービスが好きな人など、熱意ある人々と一緒に、最高のプロダクトやサービスを作っていきたいと考えております。 興味を持っていただけた方は、弊社ウェブサイトから、または私の DM にでも、お気軽にご連絡いただければと思っております！

  1. fish だとhistory | awk '{print $1}' | sort | uniq -c | sort -nr | awk 'NR<=10 {print $2}' | cat -nです。zsh だと history -E 1 | awk '{print $4}' | sort | uniq -c | sort -nr | awk 'NR<=10 {print $2}' | cat -nらしいです↩

  2. 平均年齢 = 28歳 = 私↩

  3. 前職では私が最年少で、今より低レイヤーなミドルウェアの開発をしていたため、コンソールの住人が多かったです（必ずしも一般的な傾向ではないかもしれません）↩

  4. 異論は認めます。推しメタコマンドある方は Twitter で教えてください↩

  5. AppBrew では BI ツールとして主に Redash を使っています。Redash さんの自動整形機能はかなりお茶目です↩

  6. \o, \g のいずれも、ファイル名の代わりにコマンド名を引数として渡すと、出力をそのコマンドにパイプで渡してくれます（活用できたことはない）↩

  7. 本当は怖い"本当の Markdown"↩

  8. 架空のデータです↩

  9. 架空のデータです↩

  10. psql 9.6 で入った機能です↩

  11. 架空のデータなんですが↩