PostgreSQL

エンジニアのためのWebチートシート

psql でデータベースに接続する方法です。

# 接続
psql -U username -d dbname
psql -U username -h localhost -p 5432 -d dbname
psql "postgresql://user:pass@host:5432/dbname"
sudo -u postgres psql

# 接続情報の確認
\conninfo

# データベース切替
\c other_db

# 終了
\q

データベースの構造を確認するメタコマンドです。

\l            # データベース一覧
\dt           # テーブル一覧
\dt+          # テーブル一覧（詳細）
\d table_name # テーブル構造
\d+ table_name # テーブル構造（詳細）
\di           # インデックス一覧
\dv           # ビュー一覧
\ds           # シーケンス一覧
\df           # 関数一覧
\dn           # スキーマ一覧
\du           # ロール/ユーザー一覧
\dx           # 拡張機能一覧
\h SELECT     # SQLコマンドヘルプ
\?            # psqlコマンド一覧

psql の実用的な操作コマンドです。

\i /path/to/file.sql   # SQLファイル実行
\x                      # 拡張表示トグル
\timing                 # 実行時間の表示
\! ls -la               # シェルコマンド実行
\e                      # エディタでクエリ編集

# 出力をファイルに保存
\o output.txt
SELECT * FROM users;
\o

# CSV エクスポート / インポート
\copy users TO '/path/to/users.csv' CSV HEADER
\copy users FROM '/path/to/users.csv' CSV HEADER

配列型の定義と操作です。

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  tags TEXT[]
);

INSERT INTO posts (tags)
VALUES (ARRAY['postgresql', 'database']);
INSERT INTO posts (tags)
VALUES ('{sql,nosql}');

-- 包含チェック
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];
SELECT * FROM posts WHERE 'sql' = ANY(tags);

-- 配列操作
SELECT array_length(ARRAY[1,2,3], 1);  -- 3
SELECT ARRAY[1,2] || ARRAY[3,4];       -- {1,2,3,4}
SELECT unnest(ARRAY['a','b','c']);
SELECT array_agg(name) FROM users;

jsonb 型の基本操作です。json よりも jsonb を推奨します。

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home", "count": 5}');

-- 値の取得
SELECT data -> 'type' FROM events;      -- "click"（JSON型）
SELECT data ->> 'type' FROM events;     -- click（テキスト型）
SELECT data #>> '{user,name}' FROM events; -- ネストアクセス
SELECT (data ->> 'count')::int FROM events; -- 型キャスト

-- json: テキスト保存、高速な挿入
-- jsonb: バイナリ保存、高速な検索、インデックス対応

テーブルの作成です。GENERATED ALWAYS AS IDENTITY が推奨されます。

-- GENERATED ALWAYS AS IDENTITY（推奨）
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  age INT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- SERIAL（レガシー）
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

DROP TABLE IF EXISTS users CASCADE;
TRUNCATE TABLE users RESTART IDENTITY CASCADE;
ALTER TABLE users RENAME TO members;

カラムの追加、変更、削除です。

ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;

-- NOT NULL 制約
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- デフォルト値
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT NOW();
ALTER TABLE users ALTER COLUMN created_at DROP DEFAULT;

主キー、外部キー、CHECK制約などです。

-- 主キー
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- 外部キー
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

-- 複合主キー
CREATE TABLE user_roles (
  user_id INT REFERENCES users(id),
  role_id INT REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

ALTER TABLE users
ADD CONSTRAINT uq_name_email UNIQUE (name, email);
ALTER TABLE users DROP CONSTRAINT uq_email;

RETURNING

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, name;

UPDATE users SET name = 'Bob' WHERE id = 1
RETURNING *;

DELETE FROM users WHERE id = 1
RETURNING id, name;

UPSERT (ON CONFLICT)

-- 衝突時に更新
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- 衝突時に何もしない
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- 条件付き UPSERT
INSERT INTO users (email, name, updated_at)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at
WHERE users.name != EXCLUDED.name;

DISTINCT ON / ILIKE

-- DISTINCT ON（各グループの最初の行を取得）
SELECT DISTINCT ON (department)
  department, name, salary
FROM employees
ORDER BY department, salary DESC;

-- ILIKE（大文字小文字を無視した LIKE）
SELECT * FROM users WHERE name ILIKE '%alice%';

FILTER / GENERATE_SERIES

SELECT '42'::integer;       -- 型キャスト
SELECT NOW()::date;
SELECT '{"a":1}'::jsonb;

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE active) AS active_count,
  AVG(salary) FILTER (WHERE department = 'eng') AS eng_avg
FROM employees;

SELECT generate_series(1, 10);
SELECT generate_series(
  '2024-01-01'::date, '2024-12-31'::date,
  '1 month'::interval
);

LATERAL JOIN

-- 各ユーザーの直近3件の注文を取得
SELECT u.name, recent.*
FROM users u
LEFT JOIN LATERAL (
  SELECT total, created_at
  FROM orders WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

UNION / INTERSECT / EXCEPT

SELECT name FROM employees
UNION SELECT name FROM contractors;

SELECT name FROM employees
INTERSECT SELECT name FROM managers;

SELECT name FROM employees
EXCEPT SELECT name FROM managers;

STRING_AGG / ARRAY_AGG

SELECT department,
  STRING_AGG(name, ', ' ORDER BY name)
FROM employees GROUP BY department;

SELECT department,
  ARRAY_AGG(name ORDER BY name)
FROM employees GROUP BY department;

JSONB データの検索と更新です。

SELECT * FROM events WHERE data ? 'type';
SELECT * FROM events WHERE data ->> 'type' = 'click';
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM events WHERE data #>> '{user,name}' = 'Alice';
SELECT * FROM events WHERE (data ->> 'count')::int > 10;

-- 値の更新
UPDATE events
SET data = jsonb_set(data, '{count}', '10')
WHERE id = 1;

-- キーの追加 / 削除
UPDATE events SET data = data || '{"new_key": "value"}' WHERE id = 1;
UPDATE events SET data = data - 'old_key' WHERE id = 1;

JSONB の主要な関数です。

SELECT jsonb_set('{"a":1,"b":2}'::jsonb, '{a}', '100');
-- {"a": 100, "b": 2}

SELECT jsonb_strip_nulls('{"a":1,"b":null}'::jsonb);
-- {"a": 1}

SELECT * FROM jsonb_each('{"a":1,"b":2}'::jsonb);
SELECT * FROM jsonb_array_elements('[1,2,3]'::jsonb);
SELECT jsonb_object_keys('{"a":1,"b":2}'::jsonb);

SELECT to_jsonb(row) FROM (
  SELECT name, email FROM users LIMIT 1
) row;

SELECT jsonb_agg(to_jsonb(u)) FROM users u;

SELECT jsonb_build_object(
  'name', name, 'email', email
) FROM users;

ROW_NUMBER / RANK

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

PARTITION BY / LAG / LEAD

SELECT department, name, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev,
  LEAD(revenue) OVER (ORDER BY date) AS next
FROM daily_sales;

累計・移動平均 / NTILE

SELECT date, revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (
    ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS weekly_avg
FROM daily_sales;

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

WITH句を使った可読性の高いサブクエリです。

WITH active_users AS (
  SELECT * FROM users
  WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM active_users WHERE country = 'JP';

-- 複数 CTE
WITH
  high_spenders AS (
    SELECT user_id, SUM(total) AS spent
    FROM orders GROUP BY user_id
    HAVING SUM(total) > 100000
  ),
  recent_users AS (
    SELECT * FROM users
    WHERE created_at > NOW() - INTERVAL '1 year'
  )
SELECT r.name, h.spent
FROM recent_users r JOIN high_spenders h ON r.id = h.user_id;

-- CTE で DELETE + INSERT
WITH deleted AS (
  DELETE FROM sessions WHERE expired_at < NOW()
  RETURNING *
)
INSERT INTO session_archive SELECT * FROM deleted;

階層データやツリー構造をたどる再帰クエリです。

-- 階層構造（組織図など）
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

-- 連番生成
WITH RECURSIVE nums AS (
  SELECT 1 AS n UNION ALL
  SELECT n + 1 FROM nums WHERE n < 100
)
SELECT * FROM nums;

-- カテゴリツリー（パス構築）
WITH RECURSIVE cat_tree AS (
  SELECT id, name, parent_id, name::text AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.path || ' > ' || c.name
  FROM categories c JOIN cat_tree ct ON ct.id = c.parent_id
)
SELECT * FROM cat_tree ORDER BY path;

用途別のインデックス作成パターンです。

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- 部分インデックス（条件付き）
CREATE INDEX idx_active_users
ON users (email) WHERE active = true;

-- GIN（JSONB・配列用）
CREATE INDEX idx_data_gin ON events USING GIN (data);
CREATE INDEX idx_tags_gin ON posts USING GIN (tags);

-- BRIN（時系列用）
CREATE INDEX idx_logs_brin ON logs USING BRIN (created_at);

-- 並行作成（テーブルロックなし）
CREATE INDEX CONCURRENTLY idx_name ON table_name (column);

DROP INDEX IF EXISTS idx_name;
REINDEX INDEX idx_name;

EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM users WHERE id = 1;

統計・監視

SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;

SELECT pid, state, query, query_start
FROM pg_stat_activity WHERE state = 'active';

SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT pid, NOW() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' ORDER BY duration DESC;

ユーザー/ロールの作成と管理です。

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret';
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'secret';
CREATE ROLE dev WITH LOGIN PASSWORD 'secret' CREATEDB;
ALTER ROLE myuser WITH PASSWORD 'newsecret';
DROP ROLE IF EXISTS myuser;

SELECT current_user;
SELECT current_database();

\du
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles;

テーブルやスキーマへの権限付与です。

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
GRANT SELECT, INSERT, UPDATE ON users TO myuser;
GRANT ALL ON ALL TABLES IN SCHEMA public TO myuser;
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

REVOKE ALL ON DATABASE mydb FROM myuser;

-- 読み取り専用ユーザーの作成例
CREATE ROLE readonly WITH LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

BEGIN/COMMIT/ROLLBACK とセーブポイントです。

BEGIN;
  INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
  UPDATE accounts SET balance = balance - 500 WHERE name = 'Bob';
COMMIT;

BEGIN;
  DELETE FROM users WHERE id = 1;
ROLLBACK;

-- セーブポイント
BEGIN;
  INSERT INTO orders (...) VALUES (...);
  SAVEPOINT my_savepoint;
  UPDATE inventory SET stock = stock - 1 WHERE id = 1;
  ROLLBACK TO my_savepoint;
COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- デフォルト
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN READ ONLY;
  SELECT * FROM users;
COMMIT;

pg_dump / pg_restore によるバックアップと復元です。

pg_dumpall -U postgres > all_databases.sql
pg_dump -d mydb -f backup.sql           # プレーンSQL
pg_dump -Fc -d mydb -f backup.dump      # カスタム形式
pg_dump -Fd -d mydb -f backup_dir -j 4  # 並列
pg_dump -s -d mydb -f schema_only.sql   # スキーマのみ
pg_dump -a -d mydb -f data_only.sql     # データのみ
pg_dump -d mydb -t users -f users.sql   # 特定テーブル

# リストア
psql -d mydb < backup.sql               # プレーンSQL
pg_restore -d mydb backup.dump          # カスタム形式
pg_restore -d mydb backup_dir -j 4      # 並列リストア

createdb newdb
pg_restore -d newdb backup.dump

統計情報の更新と不要領域の回収です。

VACUUM users;
VACUUM ANALYZE users;
VACUUM FULL users;    -- テーブル再構築（排他ロック）
ANALYZE users;        -- 統計情報のみ更新
REINDEX TABLE users;
REINDEX DATABASE mydb;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

CREATE SCHEMA IF NOT EXISTS myschema;
DROP SCHEMA IF EXISTS myschema CASCADE;
SET search_path TO myschema, public;