PostgreSQLチートシート - psql・SQL・運用コマンドリファレンス

概要

PostgreSQL は ACID 準拠のオープンソースリレーショナルデータベースです。標準 SQL への高い準拠度、豊富なデータ型（JSONB、配列、範囲型、地理空間型）、強力な拡張機能（PostGIS、pg_trgm など）、堅牢な MVCC によるトランザクション制御を特徴とし、Web アプリケーションから分析基盤まで幅広く利用されています。本チートシートは現行系列（PostgreSQL 16/17）を対象に、日常的に使う構文とコマンドを整理します。

psql 接続

psql メタコマンド

データ型

DDL（テーブル定義）

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  name        text NOT NULL,
  age         integer CHECK (age >= 0),
  is_active   boolean NOT NULL DEFAULT true,
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_metadata ON users USING gin (metadata);

変更系

ALTER TABLE users ADD COLUMN phone text;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name TYPE varchar(100);
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT false;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO accounts;

DROP TABLE IF EXISTS users CASCADE;
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

外部キー

CREATE TABLE posts (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id  bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title    text NOT NULL,
  body     text
);

DML（CRUD）

-- INSERT
INSERT INTO users (email, name) VALUES ('a@example.com', 'Alice');
INSERT INTO users (email, name) VALUES
  ('b@example.com', 'Bob'),
  ('c@example.com', 'Carol')
RETURNING id, created_at;

-- SELECT
SELECT id, name FROM users WHERE is_active = true ORDER BY created_at DESC LIMIT 10;

-- UPDATE
UPDATE users SET name = 'Alicia', updated_at = now() WHERE id = 1;

-- DELETE
DELETE FROM users WHERE is_active = false;

-- UPSERT
INSERT INTO users (email, name) VALUES ('a@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

JOIN

SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id
ORDER BY post_count DESC;

集約・ウィンドウ関数

SELECT
  user_id,
  COUNT(*) AS posts,
  AVG(LENGTH(body)) AS avg_len
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5;

-- ウィンドウ関数
SELECT
  id,
  user_id,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn,
  RANK()       OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rnk,
  LAG(title)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_title
FROM posts;

CTE（共通テーブル式）

WITH recent AS (
  SELECT * FROM posts WHERE created_at > now() - interval '7 days'
)
SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

-- 再帰 CTE
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

JSONB 操作

SELECT metadata->>'role' AS role FROM users WHERE metadata @> '{"plan":"pro"}';
UPDATE users SET metadata = metadata || '{"verified":true}' WHERE id = 1;
CREATE INDEX idx_users_meta ON users USING gin (metadata jsonb_path_ops);

インデックス

CREATE INDEX idx_posts_user_created ON posts (user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));
CREATE INDEX idx_posts_title_trgm ON posts USING gin (title gin_trgm_ops);
CREATE INDEX idx_posts_active ON posts (created_at) WHERE deleted_at IS NULL;

トランザクション

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 失敗した場合
ROLLBACK;

-- セーブポイント
BEGIN;
SAVEPOINT s1;
-- ...
ROLLBACK TO s1;
COMMIT;

BEGIN ISOLATION LEVEL SERIALIZABLE;

ロック

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;

ロール / 権限

CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;

ALTER ROLE app_user WITH PASSWORD 'newsecret';
DROP ROLE app_user;

実用例

1. ページネーション（キーセット）

SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

2. UPSERT で集計テーブル更新

INSERT INTO daily_stats (day, count) VALUES (CURRENT_DATE, 1)
ON CONFLICT (day) DO UPDATE SET count = daily_stats.count + 1;

3. JSON 配列を行に展開

SELECT id, tag
FROM posts, LATERAL jsonb_array_elements_text(metadata->'tags') AS tag;

4. 全文検索

ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
CREATE INDEX idx_posts_tsv ON posts USING gin(tsv);

SELECT id, title FROM posts WHERE tsv @@ plainto_tsquery('simple', 'postgres tips');

5. ランダムサンプリング

SELECT * FROM users TABLESAMPLE BERNOULLI (1);

6. 重複削除

DELETE FROM users a USING users b
WHERE a.id > b.id AND a.email = b.email;

7. EXPLAIN

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

8. 一時テーブル

CREATE TEMP TABLE staging (id int, payload jsonb) ON COMMIT DROP;

9. COPY による高速ロード

\copy users(email, name) FROM 'users.csv' WITH (FORMAT csv, HEADER true)

10. パーティショニング

CREATE TABLE events (
  id bigint, occurred_at timestamptz NOT NULL, payload jsonb
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_04 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

11. マテリアライズドビュー

CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT user_id, COUNT(*) AS posts FROM posts GROUP BY user_id;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;

12. トリガで updated_at を自動更新

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

バックアップ / リストア

運用 SQL

よく使うオプション早見表

トラブルシューティング

Tips & ベストプラクティス

• 主キーは bigint GENERATED ALWAYS AS IDENTITY を使う。serial は旧式。
• タイムゾーン付き timestamptz を既定にし、timestamp は避ける。
• JSON は jsonb を使い、必要に応じて GIN インデックスを張る。
• インデックスは複合キーの順序を意識する。(user_id, created_at DESC) のように。
• EXPLAIN (ANALYZE, BUFFERS) で実行計画とバッファ使用量を確認する。
• 大量更新は CREATE INDEX CONCURRENTLY でロックを最小化。
• マイグレーションは 1 つのトランザクションで実行し、失敗時は自動ロールバックさせる。
• アプリ側ではプリペアドステートメント（パラメータバインド）を使い SQL インジェクションを防ぐ。
• 接続は PgBouncer などプーラで集約し、同時接続数を制御する。
• 監視には pg_stat_statements 拡張を有効化してスロークエリを可視化する。

参考リソース

• PostgreSQL 公式ドキュメント
• psql リファレンス
• SQL コマンドリファレンス
• JSON 関数と演算子
• パフォーマンスチューニング Wiki