Zenn
FORCIA Tech BlogPublicationへの投稿
🧙

【チートシート】psqlコマンドで全部やる

FORCIA Tech Blog 運営チーム
2024/09/20に公開
PostgreSQL
チートシート
psql
tech

こんにちは、エンジニアの水野です。

突然ですが、みなさんコマンドライン操作はお好きですか？
私は特に、直感的かつ手軽にスクリプトを書いてタスクをこなせるようなコマンドが好きです。
たとえばPostgreSQLを日常的に利用するDBプロフェッショナルは数多いるとはいえ、
「psqlコマンドをフルに使いこなしているよ」という方は意外と少ないのではないでしょうか。

ドキュメントを漁れば無限に機能が出てくるPostgreSQLですが、
なかでもpsqlコマンドは書き捨てクエリの操作だけではなく、psql自身が実行するメタコマンドとして、直感的かつ非常に手広く活用できます！
本記事では、psqlコマンドのチートシートに加え、具体的な活用法を紹介します。
※本記事はpsql 11.2versionで動作確認を行っています。

psqlとは

psqlとは、PostgreSQLに付随する対話型のCUIクライアントです。
SQL操作に頻繁に使用されますが、なかでも\から始まるものはpsql自身が実行するメタコマンドとして扱われます。
コレが非常に便利で、エディタ上でクエリを編集したり、条件分岐を指定したりといろんなことが出来ます。
メタコマンドの定義、基本的な構文規則などは公式ドキュメントが網羅的なので、今回は割愛します。
コマンドと言うとターミナルのシェルで実行するイメージが一般的ですが、
今回はpsqlコマンドを用いてデータベース(今回の例では forcia )へ接続して実行します。

psqlコマンド：はじめに

  • \?：メタコマンドヘルプ
  • \h：SQLのヘルプ
    まずはヘルプから。どっちだっけ？となりますがどちらも便利。
\?
forcia=# \?
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
  ...
\h
forcia=# \h
Available help:
  ABORT                            CREATE USER
  ALTER AGGREGATE                  CREATE USER MAPPING
  ALTER COLLATION                  CREATE VIEW
  ...
  • --version
    psqlクライアントのバージョンを確認する。
    PostgreSQL自身のバージョンと異なる場合もあるので要注意（基本的には同じメジャーバージョン間であれば問題なく動作します）。
$ psql --version
psql (PostgreSQL) 11.2
  • -E
    そもそもメタコマンドの内部実装を見たいときはpsql -Eとオプションを付けて実行してあげると確認できます。

psqlコマンド：超基本編

  • \echo：標準出力
  • \o {filepath}：結果をfilepathに出力する設定（相対パス可）。\oで設定解除
  • \qecho：\oで指定したファイルに標準出力
  • \i {filepath}：ファイル実行（相対パス可）
  • \a：スペースやタブなどの使用・不使用切替え
  • \t：タプル出力モード（ヘッダーと区切り線を非表示に切替え）
超基本編
forcia=# \echo hoge
hoge
forcia=# \o ~/output.txt
forcia=# select count(*) from _data_goods;
  count  
---------
 2486743
(1 row)
forcia=# \qecho select count(*) from _data_goods;
select count(*) from _data_goods;
\q
$ cat ~/output.txt 
  count  
---------
 2486743
(1 row)
$ cat ~/query.sql 
select count(*) from _data_goods;
$ psql forcia
forcia=# \i ~/query.sql
  count  
---------
 2486743
(1 row)

psqlコマンド：基本編

  • \g：直前の結果を表示
  • \gx：直前の結果を\x状態（Expanded）で表示
  • \p：直前に実行したSQLを表示
  • \w {ファイル名}：直前に実行したSQLをファイルに保存
  • \e {ファイル名}：エディタでファイルを編集し実行。ファイル名指定がなければ直前に実行した内容を編集し、実行。
  • \ef {関数名}：エディタで関数定義を編集できる。
  • \gdesc：直前に実行したSQL結果のカラム名と型を表示。
  • \H：HTML出力モードに切り替え（あまり使わないかも）
基本編
forcia=# select * from test;
-[ RECORD 1 ]-----------+------------------
name                    | test1
string | {収納家具,タンス}

forcia=# \p
select * from test;

forcia=# \gdesc
-[ RECORD 1 ]-------------------
Column | name
Type   | text
-[ RECORD 2 ]-------------------
Column | string
Type   | text[]

psqlメタコマンド：変数・条件式

  • \set {変数名} {値}：変数宣言。使用する場合は:{変数名}
\set
forcia=# \set count 5
forcia=# \echo count
count
forcia=# \echo :count
5
  • \gset {PREFIX}：直前の実行結果を変数に代入（結果が一行のときだけ）
\gset
forcia=# select count(*) from _data_goods;
  count  
---------
 2486743
(1 row)
forcia=# \gset
forcia=# \echo :count
2486743
  • \unset {変数}：変数削除
  • \if {boolean}
  • \elif {boolean}
  • \endif
    条件式をかく
test.psql
--test.psql
\set NUM 1 --変数宣言
\o /dev/null --以下のクエリ結果を書き捨てる
SELECT :NUM > 0 AS bool;
\o --標準出力に戻す
\gset misc_ --直前の実行結果（bool）を代入

\if :misc_bool
SELECT index FROM _data_goods WHERE index = 1;
\else
SELECT index FROM _data_goods WHERE index = 2;
\endif
実行結果
[forcia@localhost]$ psql forcia -f test.psql 
 index 
-----------
         1
(1 row)
  • -v：引数を渡して実行
test.psql
--test.psql
\echo :test1
\echo :test2
\echo :test3
実行結果
[forcia@hoge ~]$ psql forcia -f test.psql -v test1=onakaga -v test2= -v test3=suitana
onakaga

suitana

応用編

上記コマンドを組み合わせた一例として、テーブルデータをtsv形式でエクスポートしてレポート作成なども非常に簡単にできます。
他の言語の場合、SQL文を実行するためには、まずデータベース接続を確立し、ライブラリをインポートし、SQL文を記述して、結果を解析・操作する手順が必要です。
psqlコマンドを使用してみると、SQL結果を直接ファイルにエクスポートまで行えるので、前処理をSQL・psqlコマンド内で完結させられる点がシンプルで便利です。

  • ファイル構成
.
├── vars.sql           # 変数を定義するSQLファイル
├── query.sql          # 実行するクエリファイル
├── aggregate.sql      # 集計するSQLファイル
├── main.sql           # 上記SQLを実行するSQLファイル
└── export_report.sh   # シェルスクリプト（psqlでSQLを実行）
  • vars.sql
    変数をまとめておくファイル。接続設定・スクリプト内で使用する変数などを定義しています。
vars.sql
\set DB_NAME 'forcia'
\set DB_OWNER 'forcia'
\set ENCODING 'UTF8'
\set COLLATE 'C'
\set CTYPE 'C'
\set REPORT_DIR '/var/reports'
\set DATE '`bash -c "date +%Y%m%d"`'
\set LOG_FILE '/var/log/sales_report_' :DATE '.log'
\set MIN_SALES_THRESHOLD 1000
  • query.sql
    実行クエリを記載。最終的にvars内変数を使ってエクスポート先を指定しています。
query.sql
-- 商品ごとの当日売上を出力
\o :LOG_FILE  -- ログファイルに出力
\set MIN_SALES 100

COPY (
  SELECT product_id, SUM(sales_amount) AS total_sales
  FROM sales
  WHERE sale_date = CURRENT_DATE
  GROUP BY product_id
  HAVING SUM(sales_amount) >= :MIN_SALES
) 
TO :REPORT_DIR || '/sales_report_' || :DATE || '.csv' WITH CSV HEADER;

\o  -- 標準出力に戻す

-- ログに閾値以上の売上商品の一覧を出力
\echo 'The list of products above the sales threshold (:MIN_SALES) is being logged.'
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
WHERE sale_date = CURRENT_DATE
GROUP BY product_id
HAVING SUM(sales_amount) >= :MIN_SALES;

\if :MIN_SALES_THRESHOLD > 5000
    \echo 'export type: high'
\else
    \echo 'export type: normal'
\endif
  • aggregate.sql
    週単位の売上集計を行い、その結果をエクスポートします。
aggregate.sql
-- 週ごとの売上集計を実行
COPY (
  SELECT TO_CHAR(sale_date, 'YYYY-WW') AS sale_week, 
         SUM(sales_amount) AS weekly_sales
  FROM sales
  WHERE sale_date >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY TO_CHAR(sale_date, 'YYYY-WW')
) 
TO :REPORT_DIR || '/weekly_sales_report_' || :DATE || '.csv' WITH CSV HEADER;
  • main.sql
    上記SQLを順番にインクルードし実行します。
\i vars.sql
\echo vars.sql is completed
\i query.sql
\echo query.sql is completed
\i aggregate.sql
\echo all sql is completed
  • generate_report.sh
    上記SQLファイルをpsql経由で実行、レポートを作成します。
generate_report.sh
#!/bin/bash

# psqlを使用して変数ファイルとクエリファイルを実行
psql -h localhost -U report_user -d sales_db -f main.sql

echo "レポートが生成されました。ログは: $(cat /var/log/sales_report_$(date +"%Y%m%d").log)"

例としてこのような売上データに対して実行してみます。

  • 売上データ
forcia=# select * from sales ;
 sale_date        | product_id | sales_amount 
-------------------------+------------+--------------
 2024-08-13 01:43:06.842 |         37 |         1500
 2024-08-14 01:43:06.842 |         75 |         3147
 2024-08-15 01:43:06.842 |         19 |         2916
 2024-08-16 01:43:06.842 |         83 |         2790
 2024-08-16 01:43:06.842 |         90 |          800  -- 閾値以下
 2024-08-16 01:43:06.842 |         91 |          950  -- 閾値以下
  • 実行結果
    • sales_report_YYYYMMDD.csv
      売上が閾値（1000）以上のデータをエクスポート。
sales_report_YYYYMMDD.csv
The list of products above the sales threshold (:MIN_SALES) is being logged
37,1500
75,3147
19,2916
83,2790
export type: high
  • weekly_sales_report_YYYYMMDD.csv
    週単位の売上集計データをエクスポート。
    2024年第33週として、合計である10053を出力しています（1500 + 3147 + 2916 + 2790 + 800 + 950 = 10053）。
weekly_sales_report_YYYYMMDD.csv
2024-33,10053

上記はあくまで簡易的な例ですが、DBへの接続・クエリ実行も非常にサクッと自動化出来て便利なことがわかるかと思います。
接続設定、クエリ変数、定期的に実行するクエリについても組み合わせてcrontab設定などしてしまえば、日次レポートも自動生成できて非常に嬉しいですね。

おわりに

エンジニアが日常的に使えるツールには少なからず、使いこなすまでのハードルが高いが覚えれば非常に有用な、「一度覚えれば、いける...」ものが数多あるかと思います。
そんな中、psqlコマンドのいいところはとにかく非常に直感的なところかと思います。
変数宣言もできて条件分岐もできて、sql実行できる、実用性高く非常に楽しい。
みなさんもがんがん使いこなしていきましょう！

この記事を書いた人

水野 凜
保護猫をもらいました。もうオンコールも辛くない（かもしれない）。

FORCIA Tech Blog
FORCIA Tech BlogPublication

――「つなげれば、見える。」――膨大・複雑なデータから必要な情報を的確に探し出す検索テクノロジーを基にしたシステム開発・サービス提供並びに、コンサルティングで企業のDXを推進するフォルシア株式会社テックブログです。 エンジニア積極募集中！！

Discussion