psql コマンド
メタコマンドはサーバーではなく psql (PostgreSQL のターミナルクライアント) の中で実行されます。バックスラッシュで始まり、セミコロンは不要です。
| 機能 | コマンド |
|---|
| データベース一覧 | \l |
| 別のデータベースに接続 | \c shop |
| テーブル一覧 | \dt |
| すべてのスキーマのテーブル一覧 | \dt *.* |
| テーブルの詳細 (列、インデックス、キー) | \d users |
| ストレージとコメントを含む詳細 | \d+ users |
| スキーマ一覧 | \dn |
| ビュー一覧 | \dv |
| インデックス一覧 | \di |
| 関数一覧 | \df |
| ロール (ユーザー) 一覧 | \du |
| 現在の接続情報を表示 | \conninfo |
| ファイルから SQL を実行 | \i setup.sql |
| クエリ時間表示の切り替え | \timing |
| 拡張表示 (1 行に 1 列) | \x |
| 直前のクエリをエディタで編集 | \e |
| SQL 構文のヘルプ | \h CREATE TABLE |
| すべてのメタコマンド一覧 | \? |
| psql を終了 | \q |
ターミナルからの接続
| 機能 | コマンド |
|---|
| ユーザーを指定してデータベースに接続 | psql -U postgres -d shop |
| リモートのホストとポートに接続 | psql -h db.example.com -p 5432 -U app -d shop |
| 接続 URL で接続 | psql postgresql://app:secret@localhost:5432/shop |
| クエリを 1 つ実行して終了 | psql -d shop -c "SELECT count(*) FROM users;" |
| SQL ファイルを実行 | psql -d shop -f setup.sql |
| シェルからデータベースを作成 | createdb shop |
| シェルからデータベースを削除 | dropdb shop |
データベースとスキーマ
| 機能 | SQL |
|---|
| データベースを作成 | CREATE DATABASE shop; |
| データベースを削除 | DROP DATABASE shop; |
| 現在のデータベースを表示 | SELECT current_database(); |
| スキーマを作成 | CREATE SCHEMA sales; |
| スキーマの検索順序を表示 | SHOW search_path; |
| 指定したスキーマを先に検索 | SET search_path TO sales, public; |
| サーバーのバージョンを表示 | SELECT version(); |
テーブルとデータ型
| 機能 | SQL |
|---|
| identity キー付きのテーブルを作成 | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| 列を追加 | ALTER TABLE users ADD COLUMN phone text; |
| 列名を変更 | ALTER TABLE users RENAME COLUMN country TO country_code; |
| 列の型を変更 | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| デフォルト値を設定 | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| 列を削除 | ALTER TABLE users DROP COLUMN tags; |
| テーブルを空にして identity をリセット | TRUNCATE orders, users RESTART IDENTITY; |
| テーブルが存在すれば削除 | DROP TABLE IF EXISTS products; |
| 金額用の正確な小数 | numeric(10,2) |
| タイムゾーン付きの日時 | timestamptz |
| ランダムな UUID | SELECT gen_random_uuid(); |
| 列挙型 (enum) | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
制約とキー
| 機能 | SQL |
|---|
| 主キーを追加 | ALTER TABLE logs ADD PRIMARY KEY (id); |
| 子の行も削除する外部キー | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| CHECK 制約を追加 | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| NULL を等しいとみなす一意制約 (15 以降) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| 列を必須にする | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| 制約を削除 | ALTER TABLE users DROP CONSTRAINT users_email_key; |
クエリ (PostgreSQL 独自の機能)
標準の SELECT、WHERE、JOIN は SQL チートシートにあります。ここでは PostgreSQL 独自の書き方を紹介します。
| 機能 | SQL |
|---|
| 大文字と小文字を区別しない一致 | SELECT * FROM users WHERE name ILIKE 'a%'; |
| 正規表現による一致 | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| グループごとの最初の行 | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| LIMIT と OFFSET | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| 標準の行数制限 | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| :: でキャスト | SELECT '42'::integer + 1; |
| NULL の代替値 | SELECT name, COALESCE(email, 'none') FROM users; |
| NULL を等しいとみなして比較 | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| 連番を生成 | SELECT * FROM generate_series(1, 5); |
| 文字列に集約 | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| 条件付き集計 | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
挿入、更新、削除、upsert
| 機能 | SQL |
|---|
| 挿入して新しい id を受け取る | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| 複数行を挿入 | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| upsert (挿入または更新) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| 挿入し、重複は無視 | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| 更新して変更された行を確認 | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| 別のテーブルの値で更新 | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| 削除して削除された行を確認 | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| 別のテーブルを使って削除 | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| 行を新しいテーブルにコピー | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| 機能 | SQL |
|---|
| フィールドを JSON として取得 | SELECT profile -> 'plan' FROM users; |
| フィールドをテキストとして取得 | SELECT profile ->> 'plan' FROM users; |
| ネストしたフィールドをテキストとして取得 | SELECT profile #>> '{address,city}' FROM users; |
| JSON に値を含む行 | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| キーを持つ行 | SELECT * FROM users WHERE profile ? 'city'; |
| キーを設定 | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| オブジェクトをマージ | UPDATE users SET profile = profile || '{"beta": true}'; |
| キーを削除 | UPDATE users SET profile = profile - 'city'; |
| 列から JSON を組み立てる | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| 行を JSON 配列にする | SELECT jsonb_agg(users) FROM users; |
| @> と ? の検索用インデックス | CREATE INDEX users_profile_idx ON users USING gin (profile); |
配列
| 機能 | SQL |
|---|
| 配列リテラル | SELECT ARRAY[1, 2, 3]; |
| 配列に値を含む行 | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| 配列が指定した値をすべて含む行 | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| 要素ごとに 1 行 | SELECT name, unnest(tags) FROM users; |
| 要素を末尾に追加 | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| 配列の長さ | SELECT name, cardinality(tags) FROM users; |
日付と時刻
| 機能 | SQL |
|---|
| 現在時刻 (タイムゾーン付き) | SELECT now(); |
| 今日 | SELECT current_date; |
| 期間を加算 | SELECT current_date + INTERVAL '7 days'; |
| 過去 30 日間の行 | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| 月単位に切り捨て | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| 一部を抽出 | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| 日付の差 (日数) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| 日付間の経過期間 | SELECT name, age(current_date, hired_on) FROM employees; |
| 日付をテキストに整形 | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| テキストを日付に変換 | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| タイムゾーンを変換 | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
文字列関数
| 機能 | SQL |
|---|
| 連結 (NULL があると NULL) | SELECT name || ' <' || email || '>' FROM users; |
| NULL を飛ばして連結 | SELECT concat_ws(' ', name, email) FROM users; |
| 大文字と小文字 | SELECT upper(name), lower(name) FROM users; |
| 長さ | SELECT name, length(name) FROM users; |
| 部分文字列 | SELECT substring(name FROM 1 FOR 2) FROM users; |
| 置換 | SELECT replace(email, '@example.com', '') FROM users; |
| 区切り文字で分割して一部を取り出す | SELECT split_part(email, '@', 2) FROM users; |
| 正規表現で置換 | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| 空白を削除 | SELECT trim(' hi '); |
CTE とウィンドウ関数
| 機能 | SQL |
|---|
| WITH でサブクエリに名前を付ける | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| 再帰 CTE (5 まで数える) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| グループごとに行番号を付ける | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| 同順位ありの順位 | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| 累計 | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| 前の行の値 | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| 行ごとのサブクエリと結合 | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
インデックス
| 機能 | SQL |
|---|
| B-tree インデックス (デフォルト) | CREATE INDEX orders_user_idx ON orders (user_id); |
| 一意インデックス | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| 複数列インデックス | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| 部分インデックス | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| 式インデックス | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| カバリングインデックス (index-only scan) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| 書き込みをブロックせずに作成 | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| インデックスを削除 | DROP INDEX IF EXISTS orders_user_idx; |
| 実行計画を確認 | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| 実際の所要時間付きで実行計画を確認 | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
トランザクションとロック
| 機能 | SQL |
|---|
| 開始して、確定または取り消し | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| トランザクションの一部を取り消す | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| より厳しい分離レベル | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| 読み取った行をロック | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| ジョブキュー: ロック中の行をスキップ | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
ユーザー、ロール、権限
| 機能 | SQL |
|---|
| ログインできるロールを作成 | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| テーブルへの読み取り専用アクセス | GRANT SELECT ON users TO app; |
| スキーマ内の全テーブルへの読み取りアクセス | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| 権限を取り消す | REVOKE SELECT ON users FROM app; |
| パスワードを変更 | ALTER ROLE app PASSWORD 'new-secret'; |
| 現在のユーザーを表示 | SELECT current_user; |
メンテナンスと監視
| 機能 | SQL |
|---|
| 実行中のクエリ | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| クエリをキャンセル | SELECT pg_cancel_backend(12345); |
| 接続を終了 | SELECT pg_terminate_backend(12345); |
| インデックスを含むテーブルのサイズ | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| データベースのサイズ | SELECT pg_size_pretty(pg_database_size(current_database())); |
| プランナの統計情報を更新 | ANALYZE orders; |
| 不要になった行の領域を回収 | VACUUM orders; |
| テーブルを最小サイズに書き直す (ロックされる) | VACUUM FULL orders; |
| 設定を表示 | SHOW work_mem; |
| このセッションの設定を変更 | SET work_mem = '64MB'; |
バックアップとリストア
PostgreSQL クライアントツールのシェルコマンドです。
| 機能 | コマンド |
|---|
| データベースを SQL ファイルにダンプ | pg_dump -U postgres shop > shop.sql |
| カスタム形式でダンプ | pg_dump -Fc -U postgres shop > shop.dump |
| 1 つのテーブルをダンプ | pg_dump -t orders shop > orders.sql |
| SQL ダンプをリストア | psql -d shop -f shop.sql |
| カスタム形式のダンプをリストア | pg_restore -d shop shop.dump |
| すべてのデータベースとロールをダンプ | pg_dumpall > cluster.sql |
| クエリ結果を CSV にエクスポート (psql から) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| CSV をインポート (psql から) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
よく使う psql コマンドと PostgreSQL のステートメントを、コピペできる表にまとめました。まず接続と \d コマンド、続いてテーブル、upsert、JSONB、日付、ウィンドウ関数、インデックス、ロール、バックアップ、メンテナンスを収録しています。
ここで紹介する SQL は PostgreSQL 17 の構文で、その多くは以前のバージョンでも動作します。SQL ステートメントは PostgreSQL 17 で確認済みです。試すには、サンプルテーブル付きの PostgreSQL プレイグラウンドに貼り付けてください。