PostgreSQLをコマンドラインから使う

SQLをファイルに保存、ファイル内のSQLを実行、SQL結果をファイルに保存といった頻出処理をコマンドラインから行う方法をまとめた。

設定

~/.zshrc（bashを使う場合は~/.bashrc）に以下を追記して、デフォルトのエディタを設定しておく。

# Postgre SQL
export PSQL_EDITOR="/usr/local/bin/emacs"

追記したら、実行する。

$ source ~/.zshrc

テーブルの作成

例として使うテーブルを作っておく。

kenjih=#  create table emp(
 id int primary key,
 name varchar(32),
 sex char(1),
 age int
);
CREATE TABLE

kenjih=# \d
       List of relations
 Schema | Name | Type  | Owner  
--------+------+-------+--------
 public | emp  | table | kenjih
(1 row)

クエリの編集

\e を使うとエディタが起動し、エディタ上でクエリの編集ができる。

select
  id,
  name,
  sex,
  int
from
  emp

上のクエリを入力し保存する。ここでは~/Sql/select_emp.sqlという場所に保存することにする。
クエリの最後にセミコロンをつけておくと、保存した瞬間に実行されてしまう。この挙動が怖い場合（特にWRITE系処理）は、セミコロンはつけないようにしておくとよい。

クエリの実行
先ほど保存したファイルを実行してみる。
\i [ファイル名]
で指定したファイル内のクエリを実行できる。

kenjih-# \i ~/Sql/emp_select.sql 
 id |      name      | sex | age 
----+----------------+-----+-----
  1 | taro yamada    | M   |  25
  2 | jiro tanaka    | M   |  28
  3 | kyoko imai     | F   |  21
  4 | natsumi hoshii | F   |  30
(4 rows)

保存クエリの確認
一度保存したファイルの内容を確認したい場合は、
\e [ファイル名]
とすればよい。
基本的な使い方としては、\e ファイルで保存したクエリ内容を一度確認して、目的のものと合っていれば\i ファイル名で実行とするのがいい気がする。

クエリバッファのクリア
\e、\iを使って作業をしていると稀にクエリバッファに文字列が残って想定外の挙動になってしまうことがある。そのときは、
\r
を使うとクエリバッファをクリアすることができる。

結果表示のフォーマッティング
aligned/unalignedの切替は
\a
で設定できる。
unalignedの場合は、表の形に整形されない状態で表示される。

id|name|sex|age
1|taro yamada|M|25
2|jiro tanaka|M|28
3|kyoko imai|F|21
4|natsumi hoshii|F|30
(4 rows)

unalignedの場合は区切り文字の設定を
\f [文字]
で行うことができる。

kenjih-# \f '\t'
Field separator is " ".
kenjih-# \i ~/Sql/emp_select.sql 
id name sex age
1 taro yamada M 25
2 jiro tanaka M 28
3 kyoko imai F 21
4 natsumi hoshii F 30
(4 rows)

最後の行数の表示はいらないという場合は、
\pset fotter off
とすればよい。

kenjih-# \pset footer off
Default footer is off.
kenjih-# \i ~/Sql/emp_select.sql 
id name sex age
1 taro yamada M 25
2 jiro tanaka M 28
3 kyoko imai F 21
4 natsumi hoshii F 30

ファイル出力
デフォルトではクエリの結果は標準出力に表示される。
\o [ファイル名]
で指定したファイルに出力することができる。

kenjih-# \o /tmp/test.out
kenjih-# \i ~/Sql/emp_select.sql 

ファイルに出力されたことを確認してみる。 \! でpsql内からシェルを起動することができる。

kenjih-# \!
kenjih$ cat /tmp/test.out 
id name sex age
1 taro yamada M 25
2 jiro tanaka M 28
3 kyoko imai F 21
4 natsumi hoshii F 30