psqlを設定する
縦に整列する
selectの結果が長い時、1カラムが複数行にわたって表示されてしまい見づらい。
そこでexpanded display modeをonにすることで結果が縦に整列して表示されるようになる。
db=# \x auto
nullを表示させる
nullはデフォルトでは空文字として設定されている。そこでnullも明示して表示させる。
下の設定では、nullを¤として表示させる(任意の文字列でOK)。
db=# \pset null ¤
SQLキーワードを大文字補完する
sel<tab>と打つとSELECT<space>と補完してくれる設定。
db=# \set COMP_KEYWORD_CASE upper
デフォルト設定として設定する
~/.psqlrcファイルに上記設定を記述すると、デフォルト設定として読み込まれる。
\x auto
\pset null ¤
\set COMP_KEYWORD_CASE upper
ヘルプを表示する
SQLコマンドのヘルプ一覧を表示する。
db=# \h
ALTERコマンドなど、各種コマンドのヘルプを表示する。
db=# \h alter
psql上での\コマンドの一覧を確認する。
db=# \?
データベース情報を表示する
テーブル一覧を表示する。
db=# \dt -- テーブル一覧表示
db=# \dt user* -- userから始まるテーブル一覧表示
インデックスの一覧を表示する。
db=# \di
viewの一覧を表示する。
db=# \dv
テーブル定義を表示する。
db=# \d users
functionの一覧を表示する。
db=# \df -- function一覧表示
db=# \df *regexp* -- regexpが含まれるfunction一覧表示
クエリを作成する
クエリをエディタから作成する。
db=# \e foo.sql
ファイルに記載されたクエリを実行する。
db=# \i foo.sql
シェルコマンドを実行する。
db=# \! bar.sql
結果を比較する
結果をファイルに出力する。
db=# \o a.txt
db=# EXPLAIN SELECT * FROM users
結果をターミナルに出力するように戻す。
db=# \o
上記のように結果をファイルに出力するだけだとあまり意味が無い。
しかし２つのクエリの結果を比較したい時、\oコマンドは役に立つ。
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
データベースをクローンする
開発中、migrationの変更の度にローカルのデータベースを作っては壊しながら作業する。
その度にデータベースを一から作成すると時間がかかるため、コピー用データベースをテンプレートとして作成しておくことで時間短縮ができる。
-- コピー用DBと一緒にDB作成
$ createdb -T app_db app_db_backup
-- DBをドロップする
$ dropdb app_db
-- コピー用DBをベースにDB作成
$ createdb -T app_db_backup app_db
データをCSVファイルに出力する
やり方は2通りある。
一つ目はCOPYSQLコマンドを使用する方法。
COPY (SELECT ...)
TO '/absolute/path/export.csv'
WITH (FORMAT csv, HEADER true);
これには注意点がある。
- ファイル名を絶対パスで指定しなければならない。
- ローカルのファイルシステムにしか保存できないため、リモートデータベースでの結果をローカルに保存できない。
リモートへ接続して出力した結果をローカルに保存するためには、次のように行う。
COPY (SELECT ...)
TO STDOUT
WITH (FORMAT csv, HEADER true);
> export.csv
二つ目は\copyコマンドを使用する方法。
db=# \copy (SELECT ...) TO export.csv WITH (FORMAT csv, HEADER true, ENCODING 'sjis')
\copyコマンドの場合、相対パスへの指定ができ、また、ファイルもローカルに保存される。
ただしワンライナーで書かなければならないことが注意。