DDL（データ定義言語）

テーブル / インデックス / ビュー / スキーマの作成・変更・削除。 CREATE / ALTER / DROP / TRUNCATE / RENAME を一通り。

CREATE TABLE

CREATE TABLE users (
  id           BIGSERIAL PRIMARY KEY,
  email        TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  age          INT CHECK (age >= 0),
  is_active    BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

列の修飾子

NOT NULL — NULL 禁止
DEFAULT 値 — 省略時の値
UNIQUE — 重複禁止
PRIMARY KEY — 主キー（NOT NULL + UNIQUE 自動）
CHECK (式) — 任意の制約
REFERENCES table(col) — 外部キー
GENERATED ALWAYS AS — 計算列

主キーの書き方

-- BIGSERIAL（Postgres）
id BIGSERIAL PRIMARY KEY,

-- IDENTITY（標準 SQL）
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

-- UUID
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

-- 複合主キー
PRIMARY KEY (post_id, user_id)

外部キー

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title TEXT NOT NULL,

  -- インラインで宣言
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- または列のすぐ後ろ
CREATE TABLE posts2 (
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

ON DELETE / ON UPDATE オプション

動作	意味
CASCADE	親が消えたら子も消す
SET NULL	子の FK を NULL に
SET DEFAULT	子の FK をデフォルト値に
RESTRICT	子があれば親を消せない（即座に拒否）
NO ACTION	RESTRICT に似るがチェックは末尾

制約に名前を付ける（推奨）

CREATE TABLE products (
  id BIGSERIAL,
  sku TEXT NOT NULL,
  price NUMERIC NOT NULL,
  CONSTRAINT products_pkey PRIMARY KEY (id),
  CONSTRAINT products_sku_uniq UNIQUE (sku),
  CONSTRAINT products_price_check CHECK (price >= 0)
);

後から ALTER で操作するときに名前があると楽。

テーブルの ALTER

列追加

ALTER TABLE users ADD COLUMN birth_date DATE;
ALTER TABLE users ADD COLUMN country TEXT NOT NULL DEFAULT 'JP';

列の修正

ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;
ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;
ALTER TABLE users ALTER COLUMN bio TYPE TEXT USING bio::TEXT;

列の削除 / 改名

ALTER TABLE users DROP COLUMN birth_date;
ALTER TABLE users RENAME COLUMN name TO display_name;
ALTER TABLE users RENAME TO accounts;

制約の追加 / 削除

ALTER TABLE users
  ADD CONSTRAINT users_email_lower_check CHECK (email = LOWER(email));

ALTER TABLE users DROP CONSTRAINT users_email_lower_check;

NOT NULL を後から付ける

既存データに NULL があると失敗。3 段階で安全に:

-- 1. NULL を埋める
UPDATE users SET country = 'JP' WHERE country IS NULL;

-- 2. CHECK で違反検証（即時）
ALTER TABLE users ADD CONSTRAINT temp_check CHECK (country IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT temp_check;

-- 3. 本物の NOT NULL に変える
ALTER TABLE users ALTER COLUMN country SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT temp_check;

インデックスの DDL

CREATE INDEX users_name_idx ON users (name);
CREATE UNIQUE INDEX users_email_uniq ON users (email);
CREATE INDEX users_email_lower ON users (LOWER(email));
CREATE INDEX posts_published_idx ON posts (created_at DESC) WHERE status = 'published';

DROP INDEX users_name_idx;
ALTER INDEX users_name_idx RENAME TO users_name_old;

本番では CREATE INDEX CONCURRENTLY でロックを避ける。詳細はインデックス。

ビュー（VIEW）

SELECT 文に名前を付けたもの。テーブルのように使える:

CREATE VIEW active_users AS
SELECT id, email, name
FROM users
WHERE is_active = true;

SELECT * FROM active_users WHERE name LIKE 'A%';

DROP VIEW active_users;
CREATE OR REPLACE VIEW active_users AS ...;

マテリアライズドビュー

CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, count(*) AS post_count
FROM posts GROUP BY user_id;

CREATE UNIQUE INDEX user_stats_user_idx ON user_stats (user_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

スキーマ（名前空間）

CREATE SCHEMA app;
CREATE TABLE app.users (...);
SELECT * FROM app.users;

-- 検索パス（自動補完される）
SET search_path TO app, public;

DROP SCHEMA app CASCADE;

ENUM 型（Postgres）

CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

CREATE TABLE posts (
  ...
  status post_status NOT NULL DEFAULT 'draft'
);

-- 値追加
ALTER TYPE post_status ADD VALUE 'pending' BEFORE 'published';

値の削除や順序変更は不可。柔軟性が要るなら CHECK + TEXT or ルックアップテーブル。

シーケンス

BIGSERIAL の裏で動いている自動採番。手動でも作れる:

CREATE SEQUENCE order_no_seq START 1000 INCREMENT 1;

INSERT INTO orders (order_no, ...) VALUES (nextval('order_no_seq'), ...);

SELECT currval('order_no_seq');
SELECT setval('order_no_seq', 1500);

計算列（GENERATED）

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  price NUMERIC NOT NULL,
  tax_rate NUMERIC NOT NULL DEFAULT 0.1,
  total NUMERIC GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

-- INSERT で total は指定しない

テーブルの一時的な作成

-- セッション内だけ
CREATE TEMP TABLE temp_results AS
SELECT user_id, count(*) FROM posts GROUP BY user_id;

-- WITH（CTE）でも近いことが可能

テーブルのコピー

-- 構造 + データ
CREATE TABLE posts_backup AS SELECT * FROM posts;

-- 構造だけ
CREATE TABLE posts_empty (LIKE posts INCLUDING ALL);

-- データだけ追加
INSERT INTO posts_empty SELECT * FROM posts;

DROP / TRUNCATE / DELETE

コマンド	挙動
DELETE FROM t	行削除（FK チェック / トリガー / WAL あり、遅い）
TRUNCATE t	全行を超高速削除（DDL 扱い）
DROP TABLE t	テーブル自体を削除

DROP TABLE IF EXISTS users CASCADE;
TRUNCATE TABLE logs RESTART IDENTITY CASCADE;
DELETE FROM users WHERE created_at < '2020-01-01';

RENAME

ALTER TABLE users RENAME TO accounts;
ALTER TABLE accounts RENAME COLUMN name TO display_name;
ALTER INDEX users_pkey RENAME TO accounts_pkey;

パーティショニング

CREATE TABLE events (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

関数 / トリガー

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

CREATE TRIGGER posts_set_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

EXTENSION

CREATE EXTENSION IF NOT EXISTS pgcrypto;        -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS citext;           -- 大文字小文字無視
CREATE EXTENSION IF NOT EXISTS pg_trgm;          -- LIKE 高速化
CREATE EXTENSION IF NOT EXISTS vector;           -- pgvector
CREATE EXTENSION IF NOT EXISTS postgis;          -- 地理空間

権限（GRANT / REVOKE）

GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT ALL ON SCHEMA public TO admin_user;
GRANT EXECUTE ON FUNCTION set_updated_at() TO PUBLIC;

REVOKE INSERT ON users FROM app_user;

失敗パターン

症状	対処
ALTER で全テーブルロック	CONCURRENTLY 系 / 段階的に
NOT NULL 追加で停止	DEFAULT 付き or 段階的に
ENUM が固い	CHECK + TEXT に切替検討
制約名がランダム	CONSTRAINT 名を明示
DROP CASCADE で連鎖	PRINT で確認 / ステージで実験

DDL は履歴化

DDL はマイグレーションファイルとして残す（→ Prisma migrate / Supabase migrate）。 GUI で直接編集する習慣はチームを壊す。