こんにちは、さるまりんです。
前回は、psql だけを使って クラシック音楽のミニデータベース を作ってみました。
Docker で PostgreSQL を立てて、classical_demo というデータベースを作り、
composers と works の 2 つのテーブルを操作しました。
今回はその続きです。
- 「psql ってメタコマンドが多すぎる…」
- 「どれで何ができて、何に効くのか分からない…」
正直なところ、私自身もわからないまま雰囲気で使っていたコマンドがたくさんあります。
そこで今回は、実際に手を動かして psql のメタコマンドを一つずつ試しながら、
「これはこういうときに使えるんだな」という感覚をつかんでいきたいと思います。
前回作ったクラシック音楽 DB があるので、今日はそれを「調査用データ」として使って、
psql のメタコマンドを総ざらい していきます。
1. まずは前回のDBに接続する
前回の記事と同じコンテナが起動していれば、psql から接続するだけです。
COPY 6実行すると、今回はこんな感じになりました。
Password for user postgres:
psql (12.3, server 16.11 (Debian 16.11-1.pgdg13+1))
WARNING: psql major version 12, server major version 16.
Some psql features might not work.
Type "help" for help.ここでポイントなのが、psql クライアント（12系）とサーバー（16系）のバージョンが違う という警告が出ていることです。
後で出てくる \dp など、一部のメタコマンドでこの差が実際に影響してきます。
前回の composers / works が見えるか確認してみます。
\dt出力はこんな感じでした。
classical_demo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | composers | table | postgres
public | works | table | postgres
(2 rows)ちゃんと前回作った 2 テーブルが見えていますね。
2. テーブルの情報を“もっと詳しく”見る
● \dt+：テーブルサイズつきの一覧
前回は \dt だけでしたが、+ をつけると サイズ情報 が出てきます。
\dt+出力はこんな感じ。
classical_demo=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+-------+-------------
public | composers | table | postgres | 16 kB |
public | works | table | postgres | 16 kB |
(2 rows)実務では、
- 「どのテーブルが巨大なのか？」
- 「ログテーブルが肥大していないか？」
などをざっくり見るのに、とても役立つコマンドです。
● \d+ テーブル名：テーブルの詳細＋ストレージ情報
前回は \d composers を使いましたが、今回は + つきで、ストレージ情報まで見てみます。
\d+ composersclassical_demo=# \d+ composers
Table "public.composers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+---------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('composers_id_seq'::regclass) | plain | |
name | text | | not null | | extended | |
born | integer | | | | plain | |
died | integer | | | | plain | |
nationality | text | | | | extended | |
Indexes:
"composers_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "works" CONSTRAINT "works_composer_id_fkey" FOREIGN KEY (composer_id) REFERENCES composers(id)
Access method: heap- 列の型・
NOT NULLかどうか - シーケンス（
SERIAL）の実体 - インデックス
- 外部キーで参照されているか
- ストレージ方式（
plain/extendedなど）
まで一度に見られるので、DDL を書いた直後の確認 にもぴったりです。
● \di / \di+：インデックスの一覧
クラシック DB には今のところ主キーインデックスだけがありますが、一覧を出してみます。
\diclassical_demo=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+----------------+-------+----------+-----------
public | composers_pkey | index | postgres | composers
public | works_pkey | index | postgres | works
(2 rows)さらに、サイズを含めたいときは \di+ です。
\di+classical_demo=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------+-------+----------+-----------+-------+-------------
public | composers_pkey | index | postgres | composers | 16 kB |
public | works_pkey | index | postgres | works | 16 kB |
(2 rows)実務では、インデックスの付きすぎ／足りなさ を判断するときの材料にもなります。
3. スキーマ・権限・ユーザー…“DB全体の構造”を眺める
● \dn：スキーマ一覧
\dnclassical_demo=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)この DB では public しかありませんが、
本番 DB ではアプリケーションごとにスキーマを分けていることも多く、
ここにずらっと並びます。
● \dp：権限一覧
\dp本来は「どのテーブルに誰が SELECT / INSERT できるか」を一覧で見られる便利コマンドなのですが、
今回の環境ではエラーになってしまいました。
classical_demo=# \dp
ERROR: operator is not unique: unknown || "char"
LINE 16: E' (' || polcmd || E'):'
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.原因は、最初に出ていた警告どおり、
- psql クライアント：12系
- PostgreSQL サーバー：16系
とバージョンに差がある状態で、
サーバー側のカタログ定義（information_schema / pg_catalog 内部の VIEW）が
クライアントの想定と噛み合っていないためのようです。
✅ 実務でのポイント
メタコマンドで妙なエラーが出たときは、
まず「サーバーのバージョン」と「psql のバージョン」が揃っているかを疑うと良いです。
ここでは、psql のメタコマンドの存在だけ紹介して、
\dp の実行結果そのものは一旦ここまでにしておきます。
● \du：ユーザー（ロール）一覧
\duclassical_demo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}シングルユーザー環境なので postgres だけですね。
本番 DB では、アプリ用ロール・開発者・監視用ユーザーなど、ここに多くのロールが並びます。
4. ビュー・シーケンス・拡張をチェックする
● \dv：ビューの一覧
\dvclassical_demo=# \dv
Did not find any relations.まだビューを作っていないので当然何も出ません。
「ビューがあるはずなのに出てこない」場面で使うと、
「そもそもまだ作ってなかった」ということにこれで気づけますね。
● \ds：シーケンスの一覧
\ds今回の DB は SERIAL を使っているので、
シーケンスも自動的に作られています。
classical_demo=# \ds
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | composers_id_seq | sequence | postgres
public | works_id_seq | sequence | postgres
(2 rows)ID 採番の仕組みが気になったときや、
「このシーケンスどこで使ってるんだっけ？」と調べるときの入口になります。
● \dx：拡張（Extensions）
\dxclassical_demo=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)今回は plpgsql だけですが、
pgcrypto や uuid-ossp、pg_stat_statements などを入れている DB では、
ここに追加の拡張が並びます。
5. SQL以外にも色々見える：関数・型・設定
● \df+ now：特定の関数の詳細を見る
\df本来は \df だけで関数一覧がずらっと出ることも多いのですが、
この環境では何も出てきませんでした。
classical_demo=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)ここでは、よく使う now() 関数を ピンポイントで 見てみます。
\df+ nowclassical_demo=# \df+ now
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------+--------------------------
pg_catalog | now | timestamp with time zone | | func | stable | safe | postgres | invoker | | internal | now | current transaction time
(1 row)- * どのスキーマにあるか
- * 返り値の型
- * バルネラビリティ(vulnerability)（
stable/volatile） - * 言語（
internal/sql/plpgsqlなど）
まで一気に分かります。
アプリ側から呼んでいる関数の定義を見たいときにも役立ちます。
● \dT：データ型一覧（ユーザー定義）
\dTclassical_demo=# \dT
List of data types
Schema | Name | Description
--------+------+-------------
(0 rows)今回は ユーザー定義の型を作っていない ので何も出てきません。
自作の ENUM 型や複合型を作ると、ここに並ぶようになります。
🔍 ちなみに
システム側のデータ型まで含めて見たい場合は、
\dT+S のように S オプション（system objects）をつける方法もあります。
● \encoding：現在のエンコーディング
\encodingclassical_demo=# \encoding
UTF8マルチバイト文字を扱うときには必ず確認しておきたいポイントです。
● \set：psql内部の変数一覧
\setclassical_demo=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'classical_demo'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = 'localhost'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '16.11 (Debian 16.11-1.pgdg13+1)'
SERVER_VERSION_NUM = '160011'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 12.3 on x86_64-apple-darwin19.4.0, compiled by Apple clang version 11.0.3 (clang-1103.0.32.59), 64-bit'
VERSION_NAME = '12.3'
VERSION_NUM = '120003'このあたりをいじると、
- エコーの挙動
- エラー時の止まり方
- プロンプト表示
などを細かくカスタマイズできます。
6. psql からシェルを叩く \!
psql の中から、ちょっとだけシェルを叩きたいときに便利なのが \! です。
\! lsたとえば、こんな感じの出力になります。
classical_demo=# \! ls
total 8
drwxr-xr-x 3 salumarine staff 96 Nov 30 10:00 notes
drwxr-xr-x 5 salumarine staff 160 Nov 30 10:10 sql
-rw-r--r-- 1 salumarine staff 1024 Nov 30 10:12 works.csv- エクスポートした CSV ファイルができているか確認したり
- 一時ディレクトリの中身をざっと見る
といった「軽い確認」にとても便利です。
（もちろん本番環境では、打つコマンドには十分注意が必要です。）
7. \copy で CSV を出力してみる
通常の COPY は「サーバー側のパス」を使いますが、
\copy は「psql を実行しているクライアント側のパス」を使います。
ローカルで試す場合はこちらのほうが扱いやすいです。
● SELECT 結果 → CSV
\copy (SELECT * FROM works ORDER BY year) TO './works.csv' CSV HEADER;実行結果はこんな感じ。
classical_demo=# \copy (SELECT * FROM works ORDER BY year) TO './works.csv' CSV HEADER;
COPY 6ファイルの中身を cat してみると：
% cat ./works.csv
id,composer_id,title,genre,year,duration_min
3,2,"Requiem in D minor, K.626",Requiem,1791,50
4,2,"The Magic Flute, K.620",Opera,1791,160
2,1,"Piano Sonata No.14 ""Moonlight""",Piano Sonata,1801,15
1,1,"Symphony No.5 in C minor, Op.67",Symphony,1808,35
6,3,"Piano Concerto No.1 in E minor, Op.11",Piano Concerto,1830,40
5,3,"Nocturne in E-flat major, Op.9-2",Nocturne,1832,5ちゃんとクラシック作品が CSV になっていて、ちょっと嬉しくなりますね。
● CSV → テーブルに取り込む
逆に、CSV からテーブルに読み込むこともできます。
\copy composers FROM './composers.csv' CSV HEADER;今回は composers.csv を用意していなかったので実行はしていませんが、
- 別環境からエクスポートした CSV を読み込む
- 軽いサンプルデータを流し込む
といった用途でよく使うパターンです。
8. .psqlrc に便利設定を仕込む
最後に、psql をよく使う人におすすめな .psqlrc について少しだけ。
.psqlrc は、psql 起動時に自動で読み込まれる設定ファイル です。
- 場所はユーザーのホームディレクトリ（例：
~/.psqlrc） - 毎回起動のたびに、ここに書いた設定が適用される
というイメージです。
今回触ったメタコマンドの中から、
「いつもオンにしておくと便利」なものを .psqlrc に入れてみます。
● デフォルトで縦表示をオンに
\x auto実行すると：
classical_demo=# \x auto
Expanded display is used automatically.となり、カラムが多い結果や長い文字列 があるときに、
自動的に縦表示にしてくれるようになります。
● タイミング計測をいつもオンに
\timing onclassical_demo=# \timing on
Timing is on.クエリがどれくらいかかっているか、常に意識できるようになります。
インデックス追加前後の比較などにも便利です。
● プロンプトの色付け（抜粋）
\set PROMPT1 '%[%033[1;36m%]%n@%/%R%[%033[0m%] 'こうしておくと、実際のターミナル上では
ユーザー名＋DB名の部分が水色で表示 されます。
今回実行したときのイメージとしては：
postgres@classical_demo=# select * from composers;
id | name | born | died | nationality
----+-------------------------+------+------+-------------
1 | Ludwig van Beethoven | 1770 | 1827 | German
2 | Wolfgang Amadeus Mozart | 1756 | 1791 | Austrian
3 | Frédéric Chopin | 1810 | 1849 | Polish
(3 rows)
Time: 1.108 ms※ 記事上では色が伝わりませんが、
ターミナル上では postgres@classical_demo=# の部分が色付きになります。
ANSI カラーコードに対応したターミナルであれば問題なく利用できます。
必要最低限ですが、
\x auto\timing on- カラープロンプト
あたりを .psqlrc に入れておくだけでも、psql 生活がだいぶ快適になります。
9. まとめ
今回は、前回作ったクラシック音楽 DB を使って、
\dt+/\d+/\di+などの「テーブル・インデックス調査系」- スキーマ・権限・ロール（
\dn,\dp,\du） - シーケンス・ビュー・拡張（
\ds,\dv,\dx） - 関数・型（
\df+ now,\dT） - 設定まわり（
\encoding,\set） - CSV 入出力（
\copy） - シェル実行（
\!） .psqlrcでのカスタマイズ
といった psql のメタコマンド を一気に触ってみました。
SQL を書かなくても、
「データベースの中身を調査する」 だけなら
psql のメタコマンドだけでかなりのことができます。
前回に続いて、クラシック音楽 DB をきっかけに
「psql で遊びながら調べる楽しさ」を感じてもらえたら嬉しいです。
読んでくださってありがとうございました。
それでは、また！