PostgreSQL コマンドチートシート

PostgreSQL を素早く参照:psql クライアントコマンド、DDL / DML 文、join / 集約、インデックス、トランザクション、バックアップ / リストア — よく使うオプションと実用例をまとめました。

35 コマンド

接続

psql -h <host> -p <port> -U <user> -d <db>

リモート / ローカルデータベースへ対話 psql セッションを開きます。

-h host; -p 5432(既定); -U user; -d dbname; -W パスワード強制; -f file スクリプト実行; -c 1 コマンド実行

PGPASSWORD=secret psql -h db.example.com -U app -d app_production

psql -U <user> <db> --variable='ON_ERROR_STOP=1' -f <file.sql>

SQL スクリプトをデータベースに対し実行し、最初のエラーで停止します。

psql -U app -d app -v ON_ERROR_STOP=1 -f migrations/0001_init.sql

psql メタコマンド

\l[+] \c[onnect] [<db>]

データベースを一覧;その後に 1 つに接続します。

\l+ でサイズ / 表領域情報; \c db user で対象を変更

\l+; \c app_dev

\dt[+] [<pattern>]

現在のスキーマ内でパターンに一致するテーブルを一覧(既定:public)。

\dt+ でサイズ + 説明; \dt *.* ですべてのスキーマ; \d table でテーブル詳細

\dt+ public.*

\d <table|view|index|seq|matview>

オブジェクト構造を確認:列、インデックス、FK、コメント。

\d orders

\dn / \du / \dv / \di

それぞれスキーマ / ロール / ビュー / インデックスを一覧。

\du; \di+ idx_orders_user_id

\timing / \x / \? / \q

クエリ計測 / 拡張表示の切替、ヘルプ表示、セッション終了。

\timing on; \x auto; SELECT * FROM orders LIMIT 2; \q

\copy <table> FROM '<file>' DELIMITER ',' CSV HEADER

ファイルからデータを一括ロード(サーバ側ですがクライアント FS を使用)。

\copy orders(order_id,user_id,total,created_at) FROM '/tmp/orders.csv' WITH CSV HEADER

データベース / スキーマ

CREATE DATABASE <db>

テンプレートデータベース設定で新規データベースを作成します。

OWNER role; TEMPLATE template0; ENCODING 'UTF8'; LC_COLLATE

CREATE DATABASE app_dev OWNER app_user ENCODING 'UTF8' TEMPLATE template0

DROP DATABASE <db>

データベースを削除(使用中でないこと;IF EXISTS + FORCE と併用することが多い)。

DROP DATABASE WITH (FORCE); postgres 13+

DROP DATABASE IF EXISTS app_old;

CREATE SCHEMA / DROP SCHEMA

テーブル / ビューの名前空間;既定 search_path に含まれます。

CREATE SCHEMA IF NOT EXISTS audit; DROP SCHEMA staging CASCADE;

テーブル / DDL

CREATE TABLE

新しいテーブルを列 / 型 / 既定値 / 制約付きで定義。

PRIMARY KEY; FOREIGN KEY ... REFERENCES; UNIQUE; CHECK; INHERITS; PARTITION BY RANGE|LIST

CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE

列の追加 / 削除 / リネーム、型変更、制約追加、ストレージパラメータ設定。

ADD COLUMN; DROP COLUMN; RENAME TO; ADD CONSTRAINT; ALTER COLUMN ... TYPE

ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'USD', ADD CONSTRAINT chk_cur CHECK (currency IN ('USD','EUR','CNY'));

DROP TABLE / TRUNCATE

テーブルを削除(FK から参照されていれば CASCADE);TRUNCATE は高速に空に。

DROP TABLE ... CASCADE; TRUNCATE ... RESTART IDENTITY CASCADE

TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

COMMENT ON <table|column> IS '...'

データベースオブジェクトに注釈を付与 — \d+ と IDE ツールチップに表示されます。

COMMENT ON COLUMN orders.total IS 'Subtotal before tax'

DML

INSERT INTO <table> [(cols)] VALUES (...) [RETURNING ...]

行を 1 行以上挿入;RETURNING で挿入行の列を取得。

INSERT ... SELECT; ON CONFLICT DO NOTHING / DO UPDATE(upsert); RETURNING

INSERT INTO orders(user_id, total) VALUES (42, 19.95) RETURNING id, created_at;

UPDATE ... SET ... [WHERE ...] [RETURNING ...]

条件に一致する行を更新;意図的でない限り必ず WHERE で範囲を限定。

UPDATE ... FROM t2 WHERE ...; CTE(WITH ...)+ UPDATE; ... RETURNING

UPDATE orders SET status='paid' WHERE user_id = 42 AND created_at < now() - interval '7 days' RETURNING id;

DELETE FROM ... [WHERE ...] [RETURNING ...]

条件に一致する行を削除;テーブル全削除は TRUNCATE が高速。

DELETE ... USING t2 WHERE ...; RETURNING

DELETE FROM orders WHERE status='cancelled' AND created_at < now() - interval '30 days' RETURNING id;

クエリ / フィルタ

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT N OFFSET M

フィルタ、ソート、ページネーションを付けて行を読み取り。

WHERE col op ANY/ALL(array); LIMIT n OFFSET n; FETCH FIRST n ROWS ONLY; FOR UPDATE/SHARE ロック

SELECT id, status, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 OFFSET 20;

DISTINCT / GROUP BY / HAVING

重複排除、列で集約、集約結果に対する絞り込み。

GROUP BY ROLLUP(a,b); GROUPING SETS; HAVING 集約後の絞り込み; FILTER (WHERE ...) で NULL を維持

SELECT user_id, count(*) FILTER (WHERE status='paid') AS paid, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3;

結合

[INNER | LEFT | RIGHT | FULL] JOIN ... ON ...

複数テーブルの行を組合せ;LEFT JOIN は左側の不一致行を保持。

JOIN LATERAL; NATURAL JOIN; USING(col) を ON の代わりに; CROSS JOIN 直積

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email;

WITH cte AS (...) SELECT ...

サブクエリを CTE として実体化;再帰 CTE で階層 / グラフを扱います。

WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM cats WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name FROM cats c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;

集約

count() / sum() / avg() / min() / max() / array_agg() / string_agg()

組み込みの集約関数;FILTER 句や GROUP BY と組合せると強力。

count / sum の中で DISTINCT; array_agg / string_agg 内で ORDER BY

SELECT date_trunc('day', created_at) AS day, sum(total) AS gmv FROM orders GROUP BY day ORDER BY day DESC LIMIT 7;

インデックス / ビュー

CREATE INDEX ...

WHERE / ORDER BY を高速化;部分インデックスでホットサブセットを狙えます。

CONCURRENTLY(排他ロックなし); INCLUDE (a,b) カバリング; USING btree|gin|gist|hash; 部分 WHERE

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);

EXPLAIN [ANALYZE] <query>

クエリプランを表示;ANALYZE で実行し実時間と行数を報告。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE); FORMAT JSON / TEXT / YAML

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

VACUUM / ANALYZE / REINDEX

ストレージと統計の保守:不要タプル再利用、プランナ統計更新、インデックス再構築。

VACUUM (FULL) テーブル書換; ANALYZE 統計更新; REINDEX 再構築; 通常は autovacuum が処理

VACUUM (ANALYZE) orders;

CREATE VIEW / MATERIALIZED VIEW

クエリを保存;マテリアライズドビューは実際にキャッシュ(リフレッシュ必要)。

CREATE OR REPLACE VIEW; MATERIALIZED VIEW ... WITH NO DATA; REFRESH MATERIALIZED VIEW CONCURRENTLY

CREATE MATERIALIZED VIEW mv_user_orders AS SELECT user_id, count(*) FROM orders GROUP BY user_id; CREATE UNIQUE INDEX ON mv_user_orders(user_id);

トランザクション

BEGIN / COMMIT / ROLLBACK

複数の文を 1 つのアトミック作業単位に集約(ACID)。

BEGIN ISOLATION LEVEL SERIALIZABLE; COMMIT/ROLLBACK AND CHAIN; SAVEPOINT ラベル

BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET ... WHERE ...; ROLLBACK ON ERROR; COMMIT;

SELECT ... FOR UPDATE / FOR SHARE

トランザクション終了まで該当行をロック;同時更新を防止。

FOR UPDATE / SHARE; キュー向け NOWAIT または SKIP LOCKED

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

バックアップ / リストア

pg_dump <db> -Fc -f <file.dump>

データベースをカスタム形式ファイルにダンプ(並列 / 選択的リストア)。

-Fc カスタム; -Ft tar; -Fp プレーン SQL; --schema=; --exclude-table=; -Fd 用 -j jobs

pg_dump -Fc -d app -f /backup/app-$(date +%F).dump

pg_restore -d <db> <file.dump>

カスタム / tar / dir ダンプから必要なスキーマ / データ / ロールのみリストア。

--clean 先にオブジェクト削除; --if-exists; --schema=; --table=; --jobs=N; --no-owner; --single-transaction

pg_restore -d app_dev --clean --if-exists --jobs=4 /backup/app.dump

psql -d <db> -f <file.sql>

プレーン SQL ダンプ(pg_dump -Fp の出力)をリストアします。

psql -U app -d app_new -v ON_ERROR_STOP=1 -f /backup/app.sql

ユーザ / 権限

CREATE ROLE / ALTER ROLE

ロールはログイン(LOGIN)したりオブジェクトを所有可能;属性をロール毎に付与。

CREATE ROLE reporting LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB; ALTER ROLE app SET statement_timeout = '5s'

GRANT / REVOKE

テーブル / スキーマ / データベース / 関数に対し権限を付与;列レベルも可能。

GRANT SELECT, INSERT ON TABLE ...; GRANT USAGE ON SCHEMA ...; WITH GRANT OPTION

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

\dt <schema>.* / pg_hba.conf

search_path と可視オブジェクトを調整(\dt public.*);pg_hba.conf が接続可否を制御。

SHOW search_path; ALTER ROLE app IN DATABASE app SET search_path = app, public; pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256

ALTER ROLE app SET search_path = app, public;

チートシートバージョン 1.0.0

PostgreSQL 14+ に対応