Menu
CoddyTech

PostgreSQL チートシート

最終更新

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
ランダムな UUIDSELECT 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 と OFFSETSELECT * 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 プレイグラウンドに貼り付けてください。

PostgreSQL チートシート よくある質問

psql コマンドとは何ですか?
psql は PostgreSQL のターミナルクライアントです。SQL のほかに、バックスラッシュで始まるメタコマンドを理解します。\l はデータベース一覧、\c はデータベースへの接続、\dt はテーブル一覧、\d users はテーブルの詳細、\q は終了です。psql 自体で実行されるため、セミコロンは不要で、他のクライアントでは動作しません。
PostgreSQL ですべてのテーブルを一覧表示するには?
psql では \dt (すべてのスキーマなら \dt *.*) と入力します。どのクライアントからでも使える SQL なら: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
PostgreSQL の SQL は MySQL と違いますか?
中心となる部分 (SELECT、JOIN、GROUP BY) は同じです。PostgreSQL には ILIKE、:: によるキャスト、RETURNING、DISTINCT ON、ON CONFLICT による upsert、配列、JSONB があり、MySQL が AUTO_INCREMENT を使うところでは identity 列を使います。
JSON と JSONB の違いは何ですか?
json は書かれたとおりのテキストを保存します。jsonb は解析済みのバイナリ形式で保存し、重複したキーや空白を取り除き、インデックス (GIN) や @>、? などの演算子に対応します。元のテキストをそのまま残す必要がない限り、jsonb を使ってください。
これらのクエリをオンラインで練習できますか?
はい。PostgreSQL プレイグラウンドでは、ブラウザ上で本物の PostgreSQL 17 データベースに対して任意のクエリを実行でき、編集できるサンプルテーブルも用意されています。CREATE DATABASE や CREATE ROLE のようにサーバー権限が必要なステートメントには、ご自身の PostgreSQL が必要です。
この PostgreSQL チートシートは無料ですか?

はい。無料で閲覧、コピー、印刷でき、登録も不要です。

Coddyのプログラミング言語のイラスト

CoddyでPostgreSQLを学ぼう

始める