注意:PostgreSQL限定の部分もあります。
CREATE TABLEまとめ
CREATE TABLEの基本形
CREATE TABLE テーブル名 (
-- ① カラムを定義
カラム名 データ型 カラム固有の制約,
カラム名 データ型 カラム固有の制約,
カラム名 データ型 カラム固有の制約,
-- ② テーブル全体のルール
[CONSTRAINT 制約名] 制約 (対象カラム,...),
[CONSTRAINT 制約名] 制約 (対象カラム,...)
);
※ [CONSTRAINT 制約名]は省略可能。
テーブル制約の外部キーは特殊
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id)
REFERENCES users(id),
FOREIGN KEY (role_id)
REFERENCES roles(id)
);
制約に名前を付ける意味
後から制約を変更削除するときに次のようにできるから。
ALTER TABLE users
DROP CONSTRAINT users_email_key;
よくある例1(ユーザー管理テーブル)
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
よくある例2(注文テーブル)
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
ordered_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id)
REFERENCES users(id),
FOREIGN KEY (product_id)
REFERENCES products(id),
CHECK (quantity > 0),
UNIQUE (user_id, product_id)
);
データ型一覧
| データ型 |
意味 |
例 |
主な用途 |
INTEGER |
整数 |
100, -5
|
個数、年齢、IDなど |
BIGINT |
大きな整数 |
10000000000 |
大規模なIDなど |
NUMERIC(全体の桁数, 小数点以下の桁数) |
正確な小数 |
1234.56 |
金額など |
REAL |
小数(単精度) |
3.14 |
精度が多少ずれてもよい数値 |
DOUBLE PRECISION |
小数(倍精度) |
3.141592 |
計算用の小数 |
TEXT |
長さ制限なし文字列 |
'山田太郎' |
名前、説明文など |
VARCHAR(n) |
最大n文字の文字列 |
'hello' |
文字数を制限したい場合 |
CHAR(n) |
固定長文字列 |
'AB' |
固定桁コードなど |
BOOLEAN |
真偽値 |
TRUE, FALSE
|
ON/OFF、フラグ |
DATE |
日付 |
'2026-08-12' |
誕生日など |
TIME |
時刻 |
'15:30:00' |
時刻のみ |
TIMESTAMP |
日付+時刻 |
'2026-08-12 15:30:00' |
作成日時など |
TIMESTAMPTZ |
タイムゾーン込み日時 |
'2026-08-12 15:30:00+09' |
実際の時点を扱う日時 |
UUID |
UUID |
550e8400-... |
ID |
JSONB |
JSONデータ |
'{"name":"Taro"}' |
柔軟な構造のデータ |
制約一覧
| 制約・指定 |
カラム制約 |
テーブル制約 |
意味 |
使い方の例 |
NOT NULL |
○ |
× |
NULLを禁止 |
name TEXT NOT NULL |
NULL |
○ |
× |
NULLを許可 |
nickname TEXT NULL |
PRIMARY KEY |
○ |
○ |
主キーにする |
id INTEGER PRIMARY KEY / PRIMARY KEY (id)
|
UNIQUE |
○ |
○ |
重複を禁止 |
email TEXT UNIQUE / UNIQUE (email)
|
CHECK |
○ |
○ |
条件を満たす値だけ許可 |
age INTEGER CHECK (age >= 0) |
REFERENCES |
○ |
― |
外部キーを設定 |
user_id INTEGER REFERENCES users(id) |
FOREIGN KEY (カラム名) REFERENCES 親テーブル名(親カラム名) |
× |
○ |
テーブル制約として外部キーを設定 |
FOREIGN KEY (user_id) REFERENCES users(id) |
EXCLUDE USING インデックス方式 (カラム名 WITH 演算子) |
× |
○ |
指定した条件で行同士の競合を禁止 |
EXCLUDE USING gist (room_id WITH =, period WITH &&) |
CONSTRAINT 制約名 制約内容 |
○ |
○ |
制約に名前を付ける |
CONSTRAINT users_email_key UNIQUE (email) |
指定一覧
| 指定 |
意味 |
基本形 |
使い方の例 |
DEFAULT |
値省略時の値を設定 |
DEFAULT デフォルト値 |
is_active BOOLEAN DEFAULT TRUE |
GENERATED ALWAYS AS IDENTITY |
DBに自動採番させる |
カラム名 整数型 GENERATED ALWAYS AS IDENTITY |
id INTEGER GENERATED ALWAYS AS IDENTITY |
GENERATED BY DEFAULT AS IDENTITY |
基本は自動採番、明示値も指定可能 |
カラム名 整数型 GENERATED BY DEFAULT AS IDENTITY |
id INTEGER GENERATED BY DEFAULT AS IDENTITY |
| 生成列 |
他のカラムから値を自動計算 |
GENERATED ALWAYS AS (計算式) STORED |
total INTEGER GENERATED ALWAYS AS (price * quantity) STORED |
DEFAULT などでよく使う値
| 値・式 |
意味 |
使用例 |
CURRENT_TIMESTAMP |
現在の日付+時刻 |
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP |
CURRENT_DATE |
現在の日付 |
created_date DATE DEFAULT CURRENT_DATE |
CURRENT_TIME |
現在の時刻 |
created_time TIME DEFAULT CURRENT_TIME |
TRUE |
真 |
is_active BOOLEAN DEFAULT TRUE |
FALSE |
偽 |
is_deleted BOOLEAN DEFAULT FALSE |
NULL |
値なし |
memo TEXT DEFAULT NULL |
'文字列' |
固定の文字列 |
status TEXT DEFAULT 'active' |
| 数値 |
固定の数値 |
quantity INTEGER DEFAULT 1 |
シングルクォート・ダブルクォートの違い
| 記号 |
主な用途 |
例 |
'...' |
文字列 |
'Taro' |
"..." |
識別子(テーブル名・カラム名など) |
"UserName" |
外部キーまとめ
外部キーの基本形
カラム名 データ型
REFERENCES 親テーブル名(親カラム名)
ON DELETE
親の行を削除したとき、子をどうするか決めます。
FOREIGN KEY (子カラム)
REFERENCES 親テーブル(親カラム)
ON DELETE 動作
| 指定 |
親を削除したとき |
ON DELETE CASCADE |
子の行も削除 |
ON DELETE SET NULL |
子の外部キーを NULL にする |
ON DELETE SET DEFAULT |
子の外部キーをデフォルト値にする |
ON DELETE RESTRICT |
子が存在すれば親を削除させない |
ON DELETE NO ACTION |
参照整合性に違反する削除を許可しない |
ON UPDATE
FOREIGN KEY (子カラム)
REFERENCES 親テーブル(親カラム)
ON UPDATE 動作
参照先のキーが変更されたときの動作も指定できます。
| 指定 |
親のキーが更新されたとき |
ON UPDATE CASCADE |
子の外部キーも自動更新 |
ON UPDATE SET NULL |
子の外部キーを NULL にする |
ON UPDATE SET DEFAULT |
子の外部キーをデフォルト値にする |
ON UPDATE RESTRICT |
子から参照されていれば更新を拒否 |
ON UPDATE NO ACTION |
参照整合性に違反する更新を許可しない |
CHECK
CHECK (条件式) -- 基本形
age INTEGER CHECK (age >= 0) -- 例
INSERTまとめ
INSERTの基本形
INSERT INTO テーブル名 (カラム名1, カラム名2, カラム名3)
VALUES (値1, 値2, 値3),
(値1, 値2, 値3),
(値1, 値2, 値3)
;
SELECTまとめ
SELECTの基本形
SELECT カラム名1, カラム名2
FROM テーブル名
WHERE 条件
ORDER BY カラム名 ASC または DESC
LIMIT 件数;
UPDATEまとめ
UPDATEの基本形
UPDATE テーブル名
SET カラム名 = 値
WHERE 条件;
DELETEまとめ
DELETEの基本形
DELETE FROM テーブル名
WHERE 条件;
DROPまとめ
DROPの基本形
DROPの進展系
DROP オブジェクトの種類
[IF EXISTS]
オブジェクト名
[CASCADE | RESTRICT];
| 用途 |
基本形 |
例 |
| 通常の削除 |
DROP オブジェクト種類 オブジェクト名; |
DROP TABLE users; |
| 存在する場合だけ削除 |
DROP オブジェクト種類 IF EXISTS オブジェクト名; |
DROP TABLE IF EXISTS users; |
| 依存オブジェクトも削除 |
DROP オブジェクト種類 オブジェクト名 CASCADE; |
DROP TABLE users CASCADE; |
| 依存関係があれば削除しない |
DROP オブジェクト種類 オブジェクト名 RESTRICT; |
DROP TABLE users RESTRICT; |
| 存在確認 + CASCADE |
DROP オブジェクト種類 IF EXISTS オブジェクト名 CASCADE; |
DROP TABLE IF EXISTS users CASCADE; |
| 存在確認 + RESTRICT |
DROP オブジェクト種類 IF EXISTS オブジェクト名 RESTRICT; |
DROP TABLE IF EXISTS users RESTRICT; |
削除対象の一覧
| 削除するもの |
基本形 |
例 |
| データベース |
DROP DATABASE データベース名; |
DROP DATABASE practice; |
| テーブル |
DROP TABLE テーブル名; |
DROP TABLE users; |
| スキーマ |
DROP SCHEMA スキーマ名; |
DROP SCHEMA test_schema; |
| ビュー |
DROP VIEW ビュー名; |
DROP VIEW user_summary; |
| インデックス |
DROP INDEX インデックス名; |
DROP INDEX users_email_idx; |
| シーケンス |
DROP SEQUENCE シーケンス名; |
DROP SEQUENCE users_id_seq; |
JOINまとめ
JOINの基本形
SELECT 取得するカラム
FROM テーブル1
JOIN テーブル2
ON テーブル1.カラム = テーブル2.カラム;
※JOINとだけ書いた場合はINNER JOINになる。
複数テーブルの結合の基本形
SELECT 取得するカラム
FROM テーブル1
JOIN テーブル2
ON 結合条件
JOIN テーブル3
ON 結合条件;
テーブル名の簡略化
SELECT
c.name,
e.type,
e.amount
FROM children AS c
JOIN events AS e
ON c.id = e.child_id;
JOINの種類
| JOIN |
基本形 |
意味 |
INNER JOIN |
FROM A INNER JOIN B ON 条件 |
両方に一致する行だけ |
LEFT JOIN |
FROM A LEFT JOIN B ON 条件 |
左側はすべて残す |
RIGHT JOIN |
FROM A RIGHT JOIN B ON 条件 |
右側はすべて残す |
FULL JOIN |
FROM A FULL JOIN B ON 条件 |
両方の行をすべて残す |
CROSS JOIN |
FROM A CROSS JOIN B |
全組み合わせを作る |
結合条件のパターン
| パターン |
基本形 |
例 |
用途 |
| 同じ値で結ぶ |
A.col = B.col |
children.id = events.child_id |
最も一般的 |
| 複数条件で結ぶ |
A.col1 = B.col1 AND A.col2 = B.col2 |
orders.user_id = users.id AND orders.shop_id = users.shop_id |
複合条件 |
| 不等号で結ぶ |
A.col < B.col など |
prices.amount >= discounts.min_amount |
範囲・しきい値 |
| 範囲で結ぶ |
A.col BETWEEN B.min AND B.max |
students.score BETWEEN grades.min_score AND grades.max_score |
区間に対応付ける |
| OR条件 |
条件1 OR 条件2 |
A.email = B.email OR A.phone = B.phone |
どちらか一致 |
| 式を使う |
式 = 式 |
LOWER(A.email) = LOWER(B.email) |
加工後の値で比較 |
| 固定条件も追加 |
A.id = B.id AND B.active = TRUE |
users.id = orders.user_id AND orders.active = TRUE |
結合対象を絞る |
WHEREまとめ
WHEREの基本形
SELECT 取得するカラム
FROM テーブル名
WHERE 条件;
()でくくる
WHERE (users.age >= 20 AND users.age < 30)
OR users.is_admin = TRUE
よく使う条件
| 条件 |
意味 |
例 |
= |
等しい |
WHERE users.age = 20 |
<> / !=
|
等しくない |
WHERE users.age <> 20 |
> |
より大きい |
WHERE users.age > 20 |
>= |
以上 |
WHERE users.age >= 20 |
< |
より小さい |
WHERE users.age < 20 |
<= |
以下 |
WHERE users.age <= 20 |
AND |
両方満たす |
WHERE users.age >= 20 AND users.age < 30 |
OR |
どちらか満たす |
WHERE users.age < 20 OR users.age >= 60 |
NOT |
条件を否定 |
WHERE NOT users.is_active |
IN (...) |
指定した値のどれか |
WHERE users.prefecture IN ('愛知', '東京') |
BETWEEN ... AND ... |
範囲内(両端含む) |
WHERE users.age BETWEEN 20 AND 29 |
LIKE |
パターン一致 |
WHERE users.name LIKE '田中%' |
IS NULL |
NULLである |
WHERE users.email IS NULL |
IS NOT NULL |
NULLではない |
WHERE users.email IS NOT NULL |
LIKEの使い方
| 記号 |
意味 |
例 |
マッチ例 |
% |
0文字以上の任意の文字列 |
'田中%' |
田中, 田中太郎
|
_ |
任意の1文字 |
'田_郎' |
田太郎, 田次郎
|
| 条件 |
意味 |
LIKE '田中%' |
田中で始まる |
LIKE '%太郎' |
太郎で終わる |
LIKE '%田中%' |
途中に田中を含む |
LIKE '田_郎' |
田 + 任意の1文字 + 郎
|
NOT LIKE '%田中%' |
田中を含まない |
集約関数まとめ
集約関数の基本形
SELECT
集約関数(カラム名) [AS 表示名],
集約関数(カラム名) [AS 表示名]
FROM テーブル名;
※ [AS 表示名]は省略可。
集約関数の一覧
| 集約関数 |
意味 |
基本形 |
使用例 |
COUNT(*) |
行数を数える |
COUNT(*) |
SELECT COUNT(*) FROM users; |
COUNT(カラム名) |
NULL以外の値を数える |
COUNT(users.age) |
SELECT COUNT(age) FROM users; |
COUNT(DISTINCT カラム名) |
重複を除いて数える |
COUNT(DISTINCT カラム名) |
SELECT COUNT(DISTINCT user_id) FROM orders; |
SUM(カラム名) |
合計を求める |
SUM(カラム名) |
SELECT SUM(amount) FROM orders; |
AVG(カラム名) |
平均を求める |
AVG(カラム名) |
SELECT AVG(age) FROM users; |
MAX(カラム名) |
最大値を求める |
MAX(カラム名) |
SELECT MAX(age) FROM users; |
MIN(カラム名) |
最小値を求める |
MIN(カラム名) |
SELECT MIN(age) FROM users; |
GROUP BYまとめ
GROUP BYの基本形
SELECT
グループ化するカラム,
集約関数(集計するカラム)
FROM テーブル名
GROUP BY グループ化するカラム;
GROUP BYの進展系
SELECT
グループ化するカラム [AS グループ列の別名],
集約関数(集計するカラム) [AS 集計結果の別名]
FROM テーブル名
WHERE 集約前の条件
GROUP BY グループ化するカラム
HAVING 集約後の条件
ORDER BY 並び替えるカラムまたは集約結果 ASC または DESC
LIMIT 取得件数;
※ []部分は省略可
GROUP BYの例
SELECT
orders.user_id AS user,
SUM(orders.amount) AS total_amount
FROM orders
WHERE orders.amount >= 1000
GROUP BY orders.user_id
HAVING SUM(orders.amount) >= 5000
ORDER BY total_amount DESC
LIMIT 10;
HAVINGまとめ
HAVINGの基本形
SELECT
グループ化するカラム,
集約関数(集計するカラム)
FROM テーブル名
GROUP BY グループ化するカラム
HAVING 集約結果に対する条件;
WHERE との違い
| 句 |
条件をかける対象 |
例 |
WHERE |
集約前の各行 |
WHERE orders.amount >= 1000 |
HAVING |
集約後のグループ |
HAVING SUM(orders.amount) >= 3000 |
WHEREとHAVINGを両方使う
SELECT
orders.user_id,
SUM(orders.amount) AS total_amount
FROM orders
WHERE orders.amount >= 1000
GROUP BY orders.user_id
HAVING SUM(orders.amount) >= 3000;
LIMITまとめ
LIMITの基本形
| 句 |
意味 |
例 |
LIMIT 件数 |
取得件数を制限 |
LIMIT 10 |
OFFSET 件数 |
先頭から指定件数を飛ばす |
OFFSET 20 |
LIMIT + OFFSET |
ページングなどに使う |
LIMIT 10 OFFSET 20 |
DISTINCTまとめ
DISTINCTの基本形
SELECT DISTINCT 取得するカラム
FROM テーブル名;
| 書き方 |
意味 |
SELECT DISTINCT カラム名 |
重複する値を除く |
SELECT DISTINCT カラム1, カラム2 |
カラムの組み合わせで重複を除く |
COUNT(DISTINCT カラム名) |
重複を除いた値の件数を数える |
サブクエリまとめ
サブクエリの基本形
SELECT 取得するカラム
FROM テーブル名
WHERE カラム名 演算子 (
SELECT 取得するカラム -- サブクエリ
FROM テーブル名 -- サブクエリ
WHERE 条件 -- サブクエリ
);
| 使う場所 |
基本形 |
用途 |
WHERE の中 |
WHERE col = (SELECT ...) |
条件比較 |
IN の中 |
WHERE col IN (SELECT ...) |
複数候補との比較 |
FROM の中 |
FROM (SELECT ...) AS 別名 |
一時的な表として使う |
EXISTまとめ
EXISTS は、サブクエリの結果が1件でも存在するかを判定するために使う。
EXISTの基本形
SELECT 取得するカラム
FROM テーブル名
WHERE EXISTS (
SELECT 1
FROM 別のテーブル名
WHERE 外側のテーブルと関連付ける条件
);
CASEまとめ
CASE は、条件によって返す値を変えるためのSQL式。
CASEの基本形
CASE
WHEN 条件1 THEN 値1
WHEN 条件2 THEN 値2
ELSE その他の場合の値
END
使用例
SELECT
users.name,
users.age,
CASE
WHEN users.age < 20 THEN '未成年'
WHEN users.age < 65 THEN '成人'
ELSE '高齢者'
END AS age_group
FROM users;
トランザクションまとめ
トランザクションとは、データベースに対する複数の更新処理(追加・変更・削除)を1つのまとまりとして扱い、「すべて成功させる」か「すべてなかったことにする」管理の仕組み。
トランザクションの基本形
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
SAVEPOINT after_withdraw;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
ROLLBACK TO SAVEPOINT after_withdraw;
COMMIT;
| SQL |
意味 |
基本形 |
BEGIN |
トランザクション開始 |
BEGIN; |
COMMIT |
変更を確定 |
COMMIT; |
ROLLBACK |
全変更を取り消す |
ROLLBACK; |
SAVEPOINT |
途中地点を作る |
SAVEPOINT 名前; |
ROLLBACK TO SAVEPOINT |
途中地点まで戻る |
ROLLBACK TO SAVEPOINT 名前; |
RELEASE SAVEPOINT |
SAVEPOINTを破棄 |
RELEASE SAVEPOINT 名前; |
分離レベル
BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
| 分離レベル |
概要 |
READ COMMITTED |
PostgreSQLのデフォルト |
REPEATABLE READ |
トランザクション中、同じスナップショットを基本的に見る |
SERIALIZABLE |
並行実行を直列実行したように扱えるよう厳しくチェック |
INDEXまとめ
INDEX
= 検索を速くするための索引
作るのは人間
使うか判断するのはPostgreSQL
よく使われる候補になる
- WHERE
- JOIN
- ORDER BY
注意
- INSERT / UPDATE / DELETE は重くなる
- 容量も使う
- PRIMARY KEY / UNIQUE は自動でINDEXが作られる
- FOREIGN KEY側は自動では作られない
INDEXの基本形
CREATE INDEX インデックス名
ON テーブル名 (カラム名);
| 用途 |
基本形 |
例 |
| 通常のINDEX |
CREATE INDEX 名前 ON テーブル (カラム); |
CREATE INDEX idx_users_email ON users (email); |
| 複合INDEX |
CREATE INDEX 名前 ON テーブル (カラム1, カラム2); |
CREATE INDEX idx_orders_user_date ON orders (user_id, ordered_at); |
| UNIQUE INDEX |
CREATE UNIQUE INDEX 名前 ON テーブル (カラム); |
CREATE UNIQUE INDEX idx_users_email ON users (email); |
| 条件付きINDEX |
CREATE INDEX 名前 ON テーブル (カラム) WHERE 条件; |
CREATE INDEX idx_active_users ON users (email) WHERE is_active = TRUE; |
| INDEX削除 |
DROP INDEX インデックス名; |
DROP INDEX idx_users_email; |
ALTERまとめ
ALTER は、すでに存在するデータベースオブジェクトの定義を変更するためのSQL。
ALTERの基本形
| 用途 |
基本形 |
例 |
| カラム追加 |
ADD COLUMN カラム名 データ型 |
ALTER TABLE users ADD COLUMN age INTEGER; |
| カラム削除 |
DROP COLUMN カラム名 |
ALTER TABLE users DROP COLUMN age; |
| カラム名変更 |
RENAME COLUMN 旧名 TO 新名 |
ALTER TABLE users RENAME COLUMN name TO user_name; |
| テーブル名変更 |
RENAME TO 新テーブル名 |
ALTER TABLE users RENAME TO members; |
| データ型変更 |
ALTER COLUMN カラム名 TYPE データ型 |
ALTER TABLE users ALTER COLUMN age TYPE BIGINT; |
NOT NULL追加 |
ALTER COLUMN カラム名 SET NOT NULL |
ALTER TABLE users ALTER COLUMN name SET NOT NULL; |
NOT NULL削除 |
ALTER COLUMN カラム名 DROP NOT NULL |
ALTER TABLE users ALTER COLUMN name DROP NOT NULL; |
DEFAULT追加 |
ALTER COLUMN カラム名 SET DEFAULT 値 |
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT TRUE; |
DEFAULT削除 |
ALTER COLUMN カラム名 DROP DEFAULT |
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT; |
| 制約追加 |
ADD CONSTRAINT 制約名 制約内容 |
ALTER TABLE users ADD CONSTRAINT users_age_check CHECK (age >= 0); |
| 制約削除 |
DROP CONSTRAINT 制約名 |
ALTER TABLE users DROP CONSTRAINT users_age_check; |
CREATE VIEWまとめ
ざっくりいうとSELECT文のショートカット。
CREATE VIEWの基本形
-- 基本形
CREATE VIEW ビュー名 AS
SELECT ...;
-- 作成例
CREATE VIEW milk_events AS
SELECT *
FROM events
WHERE type = 'ミルク';
-- 使用例
SELECT *
FROM milk_events;
ビューはPostgreSQLの「オブジェクト」
Database
└── Schema
├── Table
├── View
├── Sequence
├── Function
└── ...
search_pathまとめ
search_path は、スキーマ名を省略したときに、PostgreSQLがどのスキーマをどの順番で探すかを決める設定。
| 用途 |
構文 |
例 |
現在の search_path を確認 |
SHOW search_path; |
SHOW search_path; |
| 現在のスキーマを確認 |
SELECT current_schema(); |
SELECT current_schema(); |
| 現在の有効なスキーマ一覧を確認 |
SELECT current_schemas(TRUE); |
SELECT current_schemas(TRUE); |
| 一時的に設定 |
SET search_path TO スキーマ1, スキーマ2; |
SET search_path TO app, public; |
| 1つだけ設定 |
SET search_path TO スキーマ名; |
SET search_path TO app; |
| デフォルトに戻す |
RESET search_path; |
RESET search_path; |
| セッション設定をすべて戻す |
RESET ALL; |
RESET ALL; |
| ユーザー単位で恒久設定 |
ALTER ROLE ロール名 SET search_path TO ...; |
ALTER ROLE app_user SET search_path TO app, public; |
| DB単位で恒久設定 |
ALTER DATABASE DB名 SET search_path TO ...; |
ALTER DATABASE practice SET search_path TO app, public; |
psqlコマンドを代替するSQLコマンド
| psqlメタコマンド |
用途 |
SQLでの代替例 |
\l / \list
|
データベース一覧 |
SELECT datname FROM pg_database; |
\dt |
テーブル一覧 |
SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); |
\ds |
シーケンス一覧 |
SELECT schemaname, sequencename FROM pg_sequences; |
\dv |
ビュー一覧 |
SELECT table_schema, table_name FROM information_schema.views; |
\dn |
スキーマ一覧 |
SELECT schema_name FROM information_schema.schemata; |
\du |
ロール一覧 |
SELECT rolname FROM pg_roles; |
\d users |
テーブル構造確認 |
SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'users'; |
\di |
インデックス一覧 |
SELECT schemaname, tablename, indexname FROM pg_indexes; |
\conninfo |
現在の接続情報 |
SELECT current_database(), current_user, current_schema(); |
\c |
DB切り替え |
SQLでは不可。接続そのものを切り替える操作 |
\x |
拡張表示切替 |
SQLではなくpsqlの表示機能なので代替なし |
SELECT ... FOR UPDATEまとめ
取得した行を、そのトランザクションが終了するまで「更新予定の行」としてロックする。
BEGIN;
SELECT ...
FOR UPDATE;
-- 値を使った判断
UPDATE ...;
COMMIT;
解除条件
通常のSQLはautocommitであるため、;の後にコミットされる。
コミットされるとロック解除されるので、トランザクションの中で使う。
| トランザクションの終了 |
ロック |
COMMIT |
解除される |
ROLLBACK |
解除される |
使用するシチュエーション
- 残高確認 → 引き落とし
- 在庫確認 → 注文確定
- 座席の空き確認 → 予約
- ジョブ取得 → 処理開始
- ステータス確認 → 状態遷移
集合演算まとめ
Joinは横につなげるものだとしたら、集合演算は縦につなげるもの。
実際に使うときの基本ルールとして、前後のSELECTで列数を合わせる必要がある。
上記テーブルがある前提で、次を実行すると、
SELECT name
FROM A
UNION
SELECT name
FROM B;
結果は次のようになる。
| 分類 |
種類 |
SQL |
書き方 |
結果 |
意味 |
| 集合演算 |
和集合 |
UNION |
SELECT name FROM A UNION SELECT name FROM B; |
太郎、花子、次郎 |
2つの結果を縦に結合し、重複を除去 |
| 集合演算 |
和集合 |
UNION ALL |
SELECT name FROM A UNION ALL SELECT name FROM B; |
太郎、花子、花子、次郎 |
2つの結果を縦に結合し、重複を残す |
| 集合演算 |
共通部分 |
INTERSECT |
SELECT name FROM A INTERSECT SELECT name FROM B; |
花子 |
両方に存在する行だけ取得 |
| 集合演算 |
差集合 |
EXCEPT |
SELECT name FROM A EXCEPT SELECT name FROM B; |
太郎 |
AにあってBにない行だけ取得 |