2015年11月11日

コマンドラインでPostgreSQLを操作する – psqlで効率を上げる

PostgreSQL

(2015-10-25)by Philipe Fatio

本記事は、原著者の許諾のもとに翻訳・掲載しております。

私は、もう4年も毎日のようにPostgreSQLを使用しています。以前はデータベースとやりとりするためにGUIアプリケーションを用いていました。しかし今では、お気に入りのツールを使いながら効率よく作業できる、ビルトインのコマンドラインツールだけを使用しています。

本稿は、 psql というコマンドラインツールを通して実現可能なタスクを複数のセクションに分けて説明します。ここで挙げる項目は次のとおりです。

psqlの設定
ヘルプの使用
データベースが遊び場
データベースの探索
クエリの作成
出力の比較
データベースのクローン作成
データの抽出

psqlの設定

あらかじめpsqlはある程度設定されています。そのため、本稿では提供されているオプションを詳しく説明しません。psqlの使用がより楽しくなる2つのオプションについてのみ説明します。

1つ目は、データが横長でも正しくスクリーンに映し出されるようにするために重要な設定です。psqlはデフォルトでデータを整えられた表形式で出力するようになっています。

db=# SELECT 'hello' AS foo, bar FROM generate_series(1, 2) AS bar;
   foo  | bar
 -------+-----
  hello |   1
  hello |   2
 (2 rows)

ターミナルの画面幅に表示する表の横の長さが合わない場合、折り返されてしまうため、データが非常に読みにくくなってしまいます。

db=# SELECT 'really long string messing with the output' AS foo, bar,
 'another long string making things worse' AS baz FROM generate_series(1,
  2) AS bar;
                     foo                     | bar |                   baz
 ----------------------------------------+-----+-----------------------------------------
  really long string messing with the output |   1 | another long string  making things worse
  really long string messing with the output |   2 | another long string  making things worse
(2 rows)

幸いなことに、コマンドで expanded display（拡張表示） モードを有効にできます。

db=# \x 
Expanded display is on.

クエリをふたたび実行するとデータを表形式で表示しなくなります。しかし、その代わりにそれぞれのデータをKey-Valueペアの形式で表示して読みやすさを改善しています。

db=# SELECT 'really long string messing with the output' AS foo, bar,
  'another long string making things worse' AS baz FROM generate_series(1,
  2) AS bar;
 -[ RECORD 1 ]-----------------------------------
 foo | really long string messing with the output
 bar | 1
 baz | another long string making things worse 
-[ RECORD 2 ]-----------------------------------
 foo | really long string messing with the output
 bar | 2
 baz | another long string making things worse

最高に良いところは、 \x auto を実行することで、スペースがあれば表形式で表示し、なければこの形式で表示するように設定できるところです。

2つ目の重要な設定は NULL を表示する方法です。デフォルトでは NULL は表示されません。そのため、空文字列との区別がつきません。

db=# SELECT '', NULL;
  ?column? | ?column?
 ----------+----------
           |           
(1 row)

\pset null ¤ を実行することでpsqlに NULL を ¤ (あるいは他の設定した文字列)として表示する指示を出します。

db=# SELECT '', NULL;
  ?column? | ?column?
 ----------+----------
           | ¤         
¤ (1 row)

これで、空のセルは NULL ではなく空の文字列であることが分かります。

もう1つ、それほど重要ではない設定を紹介したいと思います。私のようにSQLキーワードをすべて大文字にするのを好む人には便利だと思います。psqlには基本的なタブ補完機能があります。 \set COMP_KEYWORD_CASE upper を設定することで、小文字でタイプしてもタブを押せば全て大文字で入力完了します。例えば、 sel<tab> とタイプしても、最終的に表示されるのは SELECT<space> になります。

もちろんpsqlを毎回開くたびにこれらのコマンドをタイプして環境設定するのは面倒です。幸い、psqlには ~/.psqlrc ファイルが存在すればそれを検証する機能があります。そのため、これらのコマンドを ~/.psqlrc ファイルに入れておけば、毎回のpsqlセッションは自分用の設定で実行することが可能になります。

つまり、私の ~/.psqlrc ファイルが、私のpsqlにおける作業を楽しくしてくれているのです。

\set COMP_KEYWORD_CASE upper 
\x auto 
\pset null ¤

thoughtbot のように、psql設定について徹底的に解説してくれる投稿はたくさん存在します。 psqlのドキュメントも psqlrc ファイルで使える便利なコマンドを見つけられる格好の資料です。

ヘルプの使用

使用頻度の少ないSQLコマンドの名前や起動方法を覚えていないと思います。しかし、時折使用する必要がある場合ができています。そのような場合に便利なのが \h です。

引数なしで \h と打つと、ヘルプの一覧を表示します。ヘルプの項目は SELECT や ALTER TABLE などのSQLコマンドを始める際に便利です。

例えば、列の NOT NULL 制約を解除したいけれども正確な手順を覚えていないとします。でも、 ALTER を使用すれば、大抵何かを変えることができることは覚えているとします。その場合、 \h alter を実行すれば、関連するヘルプを表示してくれます。表示されるのはかなりの量になります。

psqlではページャを使用して長いコードを表示するので、このページャの検索機能を利用します。 / の後に検索単語を入力し <enter> すれば、最初に一致するものをハイライトして、そこへジャンプします。 n や N で検索結果の項目一覧を前後にジャンプすることができます。

NULL を検索することで、すぐに ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL を見つけることができます。スクロールして上に少し移動すると、このコードが ALTER TABLE で実行されているのが分かります。これで、Googleで検索をしなくても、必要な作業を実行する情報がそろいました。

ALTER TABLE table_name ALTER column_name DROP NOT NULL;

ここまでの説明で分かるとおり、 \h はSQLコマンドで困った時に使用できるお助け機能です。しかし、psqlコマンドを助けてくれるものではありません（メタコマンドやバックスラッシュコマンドとしても知られています）。そのために、 \? があるのです。これを実行すると、全てのバックスラッシュコマンドと説明が表示されます。次のセクションでこのように便利なコマンドを説明します。

データベースが遊び場

psqlはデータベースに賢く接続します。ローカルホストや標準ポートなどの省略された接続設定のデフォルト値を推測します。そのため、psqlセッションをローカルデータベースで開始するときに psql db_name とタイプするだけでいいのです。

すごいことに、データベースの名前を省略しても、psqlは現在のユーザ名のデータベースに接続をしたいだろうと推測してくれます。そのため、コンピュータのユーザアカウント名のついたデータベースがあれば、ユーザはすぐにpsqlに入ることができます。

$ psql 
psql: FATAL:  database "phil" does not exist 
$ createdb `whoami` 
$ psql 
phil=#

このデータベースはSQL構文( \h )の検索あるいは予測の確認に最適です。 psql とタイプするだけでテストを実行し、終わりです。どのデータベースを使用するとか新たにデータベースを作成することを考える必要が全くありません。

データベースの探索

psqlには多くのバックスラッシュコマンドがあり、データベースを探索するのは簡単です。例えば、テーブルを列挙するには \dt 、インデックスを列挙するには \di 、ビューを列挙するには \dv とタイプすればいいのです。これらのすごいところは、コマンドラインでglobしてファイル名をパターンマッチングするのと同様にパターンマッチングが可能なところです。例えば、 user で始まる全てのテーブルを表示したい場合、単に \dt user* を実行すればいいのです。

私の仕事では、PostgreSQLスキーマを広範囲で利用しています。簡単に言うと、スキーマはテーブルのためのネームスペース（名前空間）のようなものです。スキーマをまだ知らない人に秘密を教えましょう。名前を知らなくても、既に使用しているものです。だた、デフォルト設定になっているものは、 public と呼ばれています。 foo というテーブルを作成すると、 public スキーマの中に作成され、 public.foo を使って参照します。私の会社では、顧客ごとにスキーマを作成し使用しています。

時にはどの顧客に特定のテーブルがあるか確認しなければなりません。例えばテーブルの名前が users だったと仮定します。 \dt *.users を実行すると、スキーマに関係なく、この名前のついた全てのテーブルが表示されます。

db=# \dt *.users 
         List of relations 
  Schema  | Name  | Type  | Owner 
----------+-------+-------+------- 
 apple    | users | table | phil 
 google   | users | table | phil 
 facebook | users | table | phil 
(3 rows)

テーブルやビューなど特定のオブジェクトの詳細がほしい場合は、 \d をオブジェクト名の後に付けてください。すると、次のような詳細が表示されます。

列とその型・ NOT NULL 制約の有無・デフォルト値
インデックス
制約の確認
外部キー制約
外部キーによってこのテーブルを参照するテーブル
トリガー

db=# \d users 
                          Table "public.users" 
  Column  |  Type   |                     Modifiers ----------+---------+---------------------------------------------------- 
 id       | integer | not null default nextval('users_id_seq'::regclass) 
 name     | text    | not null 
 email    | text    | not null 
 group_id | integer | not null 
Indexes: 
    "users_pkey" PRIMARY KEY, btree (id) 
    "users_email_key" UNIQUE CONSTRAINT, btree (email) 
Check constraints: 
    "users_email_check" CHECK (email ~ '.@.'::text) 
    "users_name_check" CHECK (name <> ''::text) 
Foreign-key constraints: 
    "users_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id) 
Referenced by: 
    TABLE "posts" CONSTRAINT "posts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

バックスラッシュコマンドは関数を使用する際に便利です。どのコマンドで関数の一覧を表示できるか考えてみましょう。お分かりだと思います。そう、 \df です。しかし他のコマンドとは違って、引数がないと \df では関数を表示することができません。おそらく関数があまりにも多いからでしょう。

時には以前使用したことがあるけど、正確な名前が思い出せない関数を探すことがあると思います。文字列の一部を置換するために正規表現関数を探したいと仮定します。 regexp を含む関数を検索してみてはどうでしょう。

db=# \df *regexp*  
                            　 List of functions 
   Schema   |      Name    | Result data type |  Argument data types   |  Type ------------+---------------+--------------+----------------------+--------
 pg_catalog | regexp_matches    | SETOF text[]   | text, text              | normal 
 pg_catalog | regexp_matches    | SETOF text[]   | text, text, text          | normal 
 pg_catalog | regexp_replace     | text           | text, text, text          | normal 
 pg_catalog | regexp_replace     | text           | text, text, text, text      | normal 
 pg_catalog | regexp_split_to_array | text[]          | text, text              | normal 
 pg_catalog | regexp_split_to_array | text[]          | text, text, text          | normal 
 pg_catalog | regexp_split_to_table | SETOF text     | text, text              | normal 
 pg_catalog | regexp_split_to_table | SETOF text     | text, text, text         | normal (8 rows)

ほら regexp_replace がありました。今まで知らなかった素敵な関数まで見つけることができました。

自分の関数を使用する際は、きちんと関数定義を読んだり、必要に応じて編集もしたいことでしょう。この時便利なのが \ef です。これは、 関数の編集（edit function） を意味します。指定する際には関数の名前を指定し、同じ名前の関数が複数存在する場合は引数の型も指定します。そして、 $EDITOR でファイルを開くことができます。関数定義は CREATE OR REPLACE FUNCTION の中にきれいにラップされています。保存したりや閉じることでファイルの中身が評価され、その関数に変更を加えることができます。

関数の中身を見たいだけの場合は、ゼロ以外の終了コードを使用してエディタを閉じれば関数に変更が加わってしまうのを避けることができます。psqlはエディタの終了コードに従うので、値がゼロでない限り評価はしません。Vimでは :cq を使用したエラーコードで終了することができます。

クエリの作成

psqlのプロンプトは短いクエリを速く書くのに最適です。クエリが大きくなるにつれSQLコマンドはプロンプトで折り返してしまったら、アップグレードしましょう。この時に便利なのが \e です。 \e を使えば自分のエディタで最後のクエリを開くことができます。使用するエディタを設定すれば、シンタックスのハイライトなどの便利な機能も利用できます。ファイルを保存し閉じると評価されます。そのため、 \e はクエリを繰り返し作成するのに最適です。

私はクエリを作業中のファイルに保存することがあります。その場合、 \e でクエリをエディタで開き、そこからクエリを自分の好きな場所に保存します。

ファイルに保存したクエリともpsqlは相性がいいのです。 \i filename を実行することで、psqlはそのファイルの中身を評価します。これは定期的に実行するクエリにも最適です。

\e は既存のファイルにもうまく機能します。ファイル名を引数として渡すことで、エディタでファイルを開くことができます。これは引数のない \e と同じで、ファイルを保存し閉じると評価されます。例えば、新しいクエリを作成している時のように、存在していないファイルの名前を特定することが残念ながらできません。1つの対応策として、最初のバージョンのクエリを直接プロンプトにタイプして、 \e を使用してエディタでクエリを開き保存する方法があります。もう1つの対応策は、 \! を使用してコマンドラインあるいはpsqlからファイルにアクセスすることで、 \! touch foo.sql のようなシェルコマンドの実行を可能にする方法です。

\i と \e 、そして特に自分で設定したエディタを組み合わせれば、クエリの繰り返しを速く便利にしてくれます。個人的には、2つの分割したtmuxを並行して置き、片方ではVimでクエリを含むファイルを編集し、もう片方ではpsqlで \i query.sql を実行するのが好きです。分割したペインを行き来しながら作業します。かつて行っていたコピペ作業やかつて使っていたJavaアプリのダサいテキスト領域と慣れないショートカットキーなどとは決別できました。

出力の比較

psqlでコマンドを実行すると結果はターミナルに出力されます。 \o コマンドによってターミナルに出力する代わりにファイル名を指定して出力することができます。 \o foo を実行することで、コマンドからの出力結果が foo ファイルに付け足されます。引数なしで \o を実行すれば出力先をターミナルに戻すことができます。

おそらく、これがどう役に立つのか気になっていると思います。この機能を使ってデータをエクスポートすることができますが、お勧めしません。正しくエクスポートする方法がありますので、これは後ほど説明します。 \o の使い道として一つ発見したのは、「複数の出力を比較したい時に使う」というものです。

自分のエディタや他のツールで2つのクエリのクエリ実行計画を比較したいと仮定します。次のように実行します。

db=# \o a.txt 
db=# EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM groups WHERE name = 'admins'); 
db=# \o b.txt 
db=# EXPLAIN SELECT users.* FROM users LEFT JOIN groups WHERE groups.name = 'admins'; 
db=# \! vimdiff a.txt b.txt

これでクエリ実行計画を並行して見ることができます。同じようなクエリ結果が実際に同じなのかそうでないのかを確認するには最適です。

データベースのクローン

時折私はデータベースのマイグレーションを作成しなければならない時があります。マイグレーションの開発過程では試行錯誤が繰り返されるので、つまり、マイグレーションを何度も実行して確認する必要がありました。これをすることで、パフォーマンスの最適化を実現し、リバースマイグレーションで確実に変更したところを元へ戻せるようにします。

マイグレーション確認のために使用するローカルデータベースは、大抵自分がマニュアルで作成したデータやプロダクションのダンプなどでいっぱいでした。マイグレーションを何度も実行するので、データやスキーマを破壊してしまう場合もあります。そうすると、データベースをまた1から作成する必要があります。データを作り直したり、読み込み直したりするのは時間がかかります。

ありがたいことに、パイを焼くように既存のデータベースを複製するのは簡単です。これでマイグレーションを初めて実行する前にバックパップを作成することができます。もしうまくいかなくても、削除しバックアップを使用して再度実行すればいいのです。

データベースを複製するには、新しいデータベースを作成し、既存のものを テンプレート に指定します。複製されたデータベースはテンプレートと全く同じです。

$ createdb -T app_db app_db_backup

バックアップを復元したい場合は、データベースを削除し新しいデータベースを作成し、バックアップをテンプレートにするだけでいいのです。

$ dropdb app_db 
$ createdb -T app_db_backup app_db

データの抽出

時折、同僚、経営管理者、クライアントとデータを共有する必要が出てきます。その場合は、CSVまたはExcelファイルを送りたいと思うことがほとんどでしょう。

必要なテーブルからデータを取り出し、結合し、整理された結果として出力できるようなクエリを用意できたとします。
では実際に、どうやってそれをデータベースから取り出すのでしょう？

Googleで、「 psql csv エクスポート 」と検索してください。すると、少なくとも2つの方法が見つかるはずです。最初に単純な方を説明しましょう。

ここではCSVファイルを出力したいのだ、ということを意識してください。CSVファイルは単純にカンマで区切られた値なので、psqlが画面にデータを出力する時の出力方法を変えたいと思う方もいるでしょう。この場合、出力を表として整形せず、セパレータにカンマ設定することでCSVに似たデータを入手することができます。しかし、これには大きな問題点があります。データにカンマが含まれる場合、それを避けることができないのです。出力される行のフィールド数が異なり、有用なCSVファイルをレンダリングできません。

CSVを適切に抽出するためには、SQLコマンドの COPY またはメタコマンドの \copy 使います。クエリの結果をCSVとしてエクスポートしたいのだと仮定しましょう。以下をご覧ください。

COPY (SELECT ...) 
TO '/absolute/path/export.csv' 
WITH (FORMAT csv, HEADER true);

COPY には、いくつかの制限があります。1つ目は絶対パスを指定しなければいけないことです。2つ目はさらに面倒なのですが、データベースが存在するローカルなファイルシステムにしか書き込むことができないということです。たとえば、リモート・データベースへのセッションでは、自分のコンピュータ上のファイルに結果を書き込めません。

このような場合に、 \copy が登場します。これは基本的に COPY のラッパで、制約を回避するものです。上記のクエリは次のように書き換えられます。

\copy (SELECT ...) TO export.csv WITH (FORMAT csv, HEADER true)

相対パスを指定、もしくはパスの指定自体を省略することもできることに注目してください。唯一の制限は、プロンプトに直接書き込む場合、全てのコマンドを一行で入力しなければならないことです。なぜなら、セミコロンではなく、改行がトリガとなってバックスラッシュコマンドが実行されるからです。しかし、これはエディタを使い、 \e を用いてクエリを入力すると適用されません。

\copy の内部では、同じクエリとオプションを含む COPY コマンドが発行されますが、ファイルではなく STDOUT に書き込まれます。するとpsqlは、出力先をローカルファイルに変更し、前述の制約を回避できるのです。

Excelでデータを利用しようと考えている場合は、必ず出力時のエンコーディングを合わせるようにしてください。デフォルトのエンコーディングはUTF-8ですが、Excelはうまく対応していません（2015年の今でさえ）。代わりにlatin1を使いましょう。

\copy (SELECT ...) TO export.csv WITH (FORMAT csv, HEADER true, ENCODING 'latin1')

クエリをファイル内に保存している場合は、ファイルの内容をcopy命令文のデータソースとして使うことはできません。残念ですが、 \copy 命令の中にクエリをコピペしなければいけません。クエリの最後にあるセミコロンを取り除くことも忘れないようにしましょう。

私は、これを繰り返すことが多すぎると気が付き、 psql2csv という小さなツールを使うことを思いつきました。これで、ファイル内に格納されたクエリを実行できるようになり（または文字列の引数として実行できるようになります）、CSVとして得た結果をSTDOUTに出力できるようになります。出力を保存することが、出力先をファイルに変更するのと同じように容易になるのです。

$ psql2csv database 'SELECT ...' > export.csv

ファイル内に保存されたクエリがある場合は、ファイルの出力先をSTDINに変更します。

$ psql2csv database < query.sql > export.csv

ツールのインターフェースはpsqlのインターフェースと全く同じです。ですから、単純にpsqlコマンドを psql2csv に置き換え、クエリの文字列を最後の引数として渡す、またはSTDIN経由で渡して有効なCSVとして結果を出力することができます。保存する場合は、単純に出力先をファイルへ変更してください。しかし、これで終わりではありません。データを他の何か面白い働きをするツールにパイプしたらどうなるでしょうか？　例えばプロットしてみてはどうでしょう？

psql2csv は、Excelの互換性が必要な場合は、 --no-header 引数や --encoding=ENCODING 引数にも対応できます。

コマンドラインから、PostgreSQLを単独で操作するのはとても素晴らしいものです。 pgAdmin を始めとして、以前はグラフィックツールで行っていた多くのことが、全てより短い時間で、好きなツールを使ってできます。もし、皆さんが何かの理由でpsqlを避けていたなら、この記事がpsqlの力を見直すきっかけになると嬉しいです。

また、この記事では取り上げなかった、psqlを使う時の特別なコツやワークフローがありましたら、ぜひ教えてください。皆さんからのご連絡をお待ちしています！