psqlのよく使うコマンド
psqlは、PostgreSQLに付属するターミナルのクライアントです。psql -h ホスト -U ユーザー -d データベースで接続し、SQLの文のほかに、バックスラッシュで始まるメタコマンドを使えます。
| コマンド | すること |
|---|---|
\l |
データベースを一覧にする |
\c dbname |
別のデータベースに接続し直す |
\dt |
今のスキーマのテーブルを一覧にする |
\d table_name |
テーブルの列、インデックス、制約を表示する |
\x |
縦長の表示に切り替える |
\timing |
文ごとの実行時間を表示する |
\i file.sql |
ファイルのSQLを実行する |
\q |
psqlを終了する |
メタコマンドはpsqlの機能で、SQLの文ではありません。このサイトのコースはブラウザーで動くデータベースを使うので、psqlのすべてのコマンドが使えるターミナルではありません。
テーブル、クエリ、インデックス
例は、使い捨てのデータベースで実行してください。identityの列がdispatchのIDを作ります。statusの制約は使える文字列を限り、NOT NULLはそれとは別に、statusがないことを拒否します。timestamptzはある瞬間を表し、表示される時刻はセッションのタイムゾーンで変わります。
CREATE TABLE dispatches (
dispatch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch text NOT NULL,
status text NOT NULL CHECK (status IN ('open', 'closed')),
scheduled_at timestamptz,
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
SELECT branch, COUNT(*) AS open_jobs
FROM dispatches
WHERE status = 'open'
GROUP BY branch
HAVING COUNT(*) > 1
ORDER BY open_jobs DESC, branch;
CREATE INDEX dispatch_open_branch
ON dispatches (branch) WHERE status = 'open';
CREATE INDEX dispatch_priority
ON dispatches ((details ->> 'priority'));
CREATE INDEX dispatch_details_gin
ON dispatches USING gin (details);
SELECT dispatch_id FROM dispatches
WHERE details @> '{"priority":"high"}'::jsonb;
BEGIN;
SAVEPOINT before_change;
UPDATE dispatches SET status = 'closed' WHERE dispatch_id = 1;
ROLLBACK TO SAVEPOINT before_change;
COMMIT;
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT dispatch_id FROM dispatches
WHERE branch = 'West' AND status = 'open';
集計のクエリは、集計の前に行を絞り込み、グループにしてからHAVINGを当てはめます。2つ目の並べ替えのキーで、件数が同じときの順番が決まります。COUNT(*)は行を数え、COUNT(column)はNULLでない値を数えます。値がないかどうかはIS NULLで調べてください。= NULLはtrueになりません。
インデックスの選び方
部分的なB-treeインデックスはopenのdispatchだけを含み、一致するbranchの絞り込みに使えます。式インデックスは取り出したpriorityの文字列を対象にします。GINインデックスは、上の包含の条件のような、対応するJSONBの操作に使われます。3つとも作っているのは構文を見せるためで、アプリにすべてのインデックスを足すことを勧めるものではありません。
どのインデックスも、ストレージと書き込みの負担が増えます。
インデックスがあっても、使われるとは限りません。B-treeインデックスと検索コストと部分インデックスと式インデックスのレッスンで、実行計画とデータの分布を確かめてください。複数列のインデックスは、演算子クラス、列の順番、実際のクエリの条件とあわせて考えます。
トランザクションと実行計画
ROLLBACK TO SAVEPOINTは、トランザクションを開いたまま更新を取り消します。COMMITでトランザクションが終わります。失敗したトランザクションでは、関係のないコマンドを出す前にロールバックしてください。シーケンスのカウンターの変更は、ふつうのロールバックの例外です。
EXPLAINは実行計画を見積もります。ANALYZEを付けると文を実際に実行するので、データを変える文なら書き込みも起きます。使い捨てのデータベースでも、試しに書き込むときは必要に応じてBEGINとROLLBACKで囲んでください。
JSONBとセキュリティ
->はJSONの値を、->>は文字列を返します。@>は包含を調べます。キーがないことと、JSONのnullは別のケースなので、既定値を決める前に演算子を選んでください。JSONBの値の取り出しと存在しないキーのレッスンで、そのケースを練習できます。
権限と行のポリシーは別のチェックです。ロールにはテーブルの権限が必要で、行のポリシーは対象になる行を絞ります。テーブルの所有者はふつう行のポリシーを素通りし、スーパーユーザーとBYPASSRLSのロールも同じです。行レベルセキュリティとテナントの切り替えで、意図したアプリのロールを使ってテストしてください。
期待する結果つきで実行できる課題は、PostgreSQLの練習問題にあります。エンジニアのためのPostgreSQLのコースでは、採点つきで練習できます。