psqlを使ってみよう
NTT オープンソースソフトウェアセンタ 板垣 貴裕
PostgreSQL に付属するコマンドライン管理ツール psql の使い方を解説していきます。psql では、対話的な操作もファイルから入力を読み込んで処理することもできます。SQL の入力補完機能の他、便利なメタコマンドも提供しています。
この記事は PostgreSQL 8.4.0 付属の psql を対象にしています。それ以前のバージョンでは一部の機能はまだ使えないかもしれません。もし古いバージョンのサーバを使い続ける必要があっても、バージョン 8.4 以降の psql ならば適切にコマンド（特にメタコマンド）を実行できるため、psql を含むクライアントツール類は最新版にアップデートしても良いでしょう。
キー操作
最初に、基本的なキー操作を一覧にします。シェルは bash を使うことが多いと思いますが、psql も基本的には bash と同様に操作することができます。
| 種別 | キー | 効果 | |
|---|---|---|---|
| 補完 | タブ (Tab) | 入力補完をします。SQL構文やテーブル名の入力候補が表示され、候補が1つしかない場合は自動的に補われます。 | |
| 移動 | Ctrl+B | Ctrl+F | カーソルを1文字 前 / 後 に移動します。 |
| Alt+B | Alt+F | カーソルを1単語 前 / 後 に移動します。 | |
| Ctrl+A | Ctrl+E | カーソルを 行頭 / 行末 に移動します。 | |
| 削除 | Ctrl+H | Ctrl+D | 現在のカーソル位置の 前方 / 後方 の1文字を削除します。 |
| Alt+D | 現在のカーソル位置の 後方 の1単語を削除します。 | ||
| Ctrl+U | Ctrl+K | 現在のカーソル位置の 前方 / 後方 すべてを削除します。 | |
| 履歴 | Ctrl+P | Ctrl+N | 入力履歴を表示します。(過去 / 未来方向) |
| Ctrl+R | Ctrl+S | 入力履歴をインクリメンタルサーチします。(過去 / 未来方向) | |
入力補完
タブキー (Tab) で入力補完が働き、SQL 構文やテーブル名などの候補を表示 or 自動的に補ってくれます。ただし、Windows のコマンドプロンプトでは補完が効きませんので、入力補完機能が使いたい場合には、Cygwin 版の psql を導入してください。
以下の例では SQL 構文 SELE... の入力補完と、テーブル名 pg_stat_a... の候補表示+補完が行われています。
=# SELE(タブ)
=# SELECT
(続きを入力)
=# SELECT * FROM pg_stat_a(タブ)
pg_stat_activity pg_stat_all_indexes pg_stat_all_tables
('c' を追加入力)
=# SELECT * FROM pg_stat_ac(タブ)
=# SELECT * FROM pg_stat_activity
メタコマンド
psql では通常の SQL を入力できますが、それ以外にもバックスラッシュ (\ または ¥; フォントに依存します) から始まるメタコマンドをサポートしています。数多くあるので全ては紹介しきれませんが、良く使うと思われるコマンドを紹介します。詳しくはPostgreSQL文書を参照してください。
¥q, ¥h, ¥? : 終了とヘルプ
psql を終了するには ¥q と入力します。ヘルプも組み込まれており、¥h は SQL の使い方を、¥? は psql そのものの使い方を表示します。SQL 構文を思い出せないときは、よく ¥h のお世話になります。
=# ¥h ALTER TABLE
¥d : オブジェクト表示
¥d から始まるメタコマンドはオブジェクトの概要を表示します。他DBMS製品では DESCRIBE などの名前になっている場合もありますが、PostgreSQL では「メタコマンドは ¥ から始まる」という明確な規定があるため、通常の SQL と区別して ¥d になっています。
使い方は、概ね以下の形式です。
¥d[種類][S][+] パターン
- S : システムオブジェクトも表示します。
- + : 詳細を表示します。
- パターン : オブジェクトの名前です。ワイルドカード (? や *) を含むことができます。使用例:
=# ¥d pgbench_* -- 名前が pgbench_ から始まるテーブルを表示 =# ¥df *regexp* -- 正規表現を扱う関数を表示
| コマンド | 覚え方 | 表示対象 |
|---|---|---|
| ¥d | (describe) | テーブル、ビュー、シーケンス |
| ¥da | aggregate | 集約関数 |
| ¥db | tablespace | テーブルスペース |
| ¥dC | Cast | キャスト |
| ¥df | function | 関数 |
| ¥di | index | インデックス |
| ¥dn | namespace | スキーマ (名前空間) |
| ¥do | operator | 演算子 |
| ¥ds | sequence | シーケンス |
| ¥dt | table | テーブル |
| ¥dT | Type | 型 |
| ¥du | user | ロール (ユーザ, グループ) |
| ¥dv | view | ビュー |
¥copy : 遠隔データロード
データロードには、一般に COPY コマンドを使います。データベース・サーバ上にあるファイルからのロードの他、COPY FROM STDIN の形式でネットワーク経由でのロードもできます。psql の ¥copy メタコマンドは、この COPY FROM STDIN の別名です。
=# ¥copy テーブル from 'ローカルパス';
SQL の COPY とは異なり、ファイル名には psql を実行するマシンでのローカルパスを指定します。このパスは絶対パス、相対パスのどちらでも構いません。
¥x : 列を縦に展開表示
列数が多く、ターミナル画面で1行が折り返されてしまう場合には、¥x で列を縦に表示させると読みやすくなります。デフォルトでは行と列を格子状に表示しますが：
=# SELECT oid, datname FROM pg_database;
oid | datname
-------+-----------
1 | template1
11564 | template0
11565 | postgres
(3 rows)
¥x で列を縦に展開する表示と切り替えることができます。もう一度入力すると元に戻ります。
=# ¥x =# SELECT oid, datname FROM pg_database; -[ RECORD 1 ]------ oid | 1 datname | template1 -[ RECORD 2 ]------ oid | 11564 datname | template0 -[ RECORD 3 ]------ oid | 11565 datname | postgres =# ¥x -- 元に戻す
Tips : 便利な使い方
雑多な psql の便利な使い方や使用する上での注意を紹介します。
繰り返しのあるSQLを実行する
内容の同じ tbl_1 ～ tbl_5 の5つのテーブルを作成するといった場合に、"実行したいSQLを生成するSQL" を使うと、作業を自動化できます。まず、"CREATE TABLE" という文字列を返すSQLを作成します。-c はコマンドライン引数をSQLとして実行するオプションです。
$ psql -c "SELECT 'CREATE TABLE tbl_' || i || (行継続)
' (id integer);' FROM generate_series(1, 5) AS t(i)"
?column?
----------------------------------
CREATE TABLE tbl_1 (id integer);
CREATE TABLE tbl_2 (id integer);
CREATE TABLE tbl_3 (id integer);
CREATE TABLE tbl_4 (id integer);
CREATE TABLE tbl_5 (id integer);
(5 rows)
これを、psql をパイプで二段重ねにすることにより、"CREATE TABLE" の SQL を直接実行することができます。この際、最初の psql にはコマンドラインオプション -A と -t を与えて、ヘッダなどの余計な表示をしないようにしておきます。
$ psql -At -c "SELECT 'CREATE TABLE tbl_' || i || (行継続) ' (id integer);' FROM generate_series(1, 5) AS t(i)" | psql
もちろん、シェルスクリプトなどのプログラミングできる環境を使っても同じことはできるのですが、「シェルスクリプトよりも SQL のほうが得意！」という方にお奨めです。
分割取得でメモリ不足防止
psql では SELECT の結果全体をメモリ上に保持しようとします。 そのため、大量のデータを一括取得する場合には、大量のメモリを消費したり、場合によってはメモリ不足 (out of memory) でエラー終了になってしまいます。 そのような場合には、FETCH_COUNT 変数を設定して、その数ずつデータを分割して取得すると良いでしょう。 以下の例では、10000行ずつ取得しており、メモリ不足になるのを避けられます。
=# ¥set FETCH_COUNT 10000 -- 10000行ずつ取得 =# ¥o data.txt -- 出力先を data.txt へ切り替え =# SELECT 大量のデータ; =# ¥o -- 出力先を元に戻す