PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：差分理解 － Day7 復習課題

SQL PostgreSQL
スポンサーリンク

Day7 前半のゴール

「“そのままコピペ”ではなく、“差分を意識して移植する”感覚を持つ」

Day7 は、ここまで学んだ差分理解の「復習課題」です。
テーマはシンプルで、MySQLで作ったDBをPostgreSQLに移植すること。

前半のゴールはこうです。
MySQLのテーブル定義を見て、「どこがPostgreSQLと違うポイントか」を目で追える。
型・自動採番・制約・インデックスの差分を、ざっくり言葉で説明できる。
「そのままコピペせず、PostgreSQL向けに“設計を少しアップデートする”」という姿勢を持てる。

ここでは、具体的な例を1つ決めて、それを題材に差分を整理していきます。

例題として使うMySQLのDB設計

「よくある“ユーザー＋記事”のシンプル構成」

まず、MySQLでよくありそうなDBを1つ決めます。
ユーザーとブログ記事、というシンプルな構成にしましょう。

MySQL版のテーブル定義を、こう置きます。

CREATE TABLE users (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(255) NOT NULL,
  name         VARCHAR(50)  NOT NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  created_at   DATETIME     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_users_email (email)
);

CREATE TABLE posts (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      INT UNSIGNED NOT NULL,
  title        VARCHAR(200) NOT NULL,
  body         TEXT         NOT NULL,
  created_at   DATETIME     NOT NULL,
  PRIMARY KEY (id),
  KEY idx_posts_user_id (user_id),
  CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users(id)
);
SQL

この中に、PostgreSQLに移すときの「差分ポイント」が詰まっています。

INT UNSIGNED＋AUTO_INCREMENT
TINYINT(1) でのフラグ
DATETIME
インデックスの書き方
外部キーの書き方

これを、PostgreSQL向けにどう変えていくかを、前半では“設計の視点”で整理します。

差分ポイント1：自動採番（AUTO_INCREMENT → SERIAL / IDENTITY）

「“カラムの属性”から“シーケンスを使うカラム”へ」

MySQLでは、主キーをこう定義していました。

id INT UNSIGNED NOT NULL AUTO_INCREMENT
SQL

PostgreSQLでは、Day4でやったように SERIAL を使うのが定番です。

id SERIAL PRIMARY KEY
SQL

あるいは、より標準SQL寄りに書くならこう。

id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
SQL

ここで大事なのは、「AUTO_INCREMENTはカラムに埋め込まれたカウンタ」「SERIAL/IDENTITYはシーケンスを使う糖衣構文」という違いを意識することです。

移植するときの基本方針はこうです。

INT UNSIGNED AUTO_INCREMENT → SERIAL または INTEGER GENERATED ... AS IDENTITY

「UNSIGNED（符号なし）」はPostgreSQLにはないので、
「負の値を使わない前提なら、普通のINTEGERで十分」と割り切ります。

差分ポイント2：フラグ型（TINYINT(1) → BOOLEAN）

「0/1の世界から、true/falseの世界へ」

MySQLでは、フラグをこう持っていました。

is_active TINYINT(1) NOT NULL DEFAULT 1
SQL

PostgreSQLには、ちゃんとBOOLEAN型があります。
Day3でやったように、こう書き換えるのが自然です。

is_active BOOLEAN NOT NULL DEFAULT true
SQL

これで、クエリもこう書けます。

SELECT * FROM users WHERE is_active;
SQL

MySQL的な WHERE is_active = 1 から、PostgreSQL的な WHERE is_active に変わることで、
SQLが「仕様書の文章」に近づきます。

移植するときの基本方針はこうです。

TINYINT(1) フラグ → BOOLEAN ＋ DEFAULT true/false

ここは「差分理解」の中でも、かなり“気持ちよさ”が出るポイントなので、意識して変えたいところです。

差分ポイント3：日時型（DATETIME → TIMESTAMPTZ / TIMESTAMP）

「“文字列っぽい日付”から、“時間として計算できる値”へ」

MySQLでは、日時をこう持っていました。

created_at DATETIME NOT NULL
SQL

PostgreSQLでは、Day3で触れたように TIMESTAMP / TIMESTAMPTZ を使います。

ローカル時間だけで完結するなら TIMESTAMP。
タイムゾーンを意識したいなら TIMESTAMPTZ（タイムゾーン付きTIMESTAMP）。

グローバルサービスや、後でタイムゾーンを扱う可能性があるなら、
最初から TIMESTAMPTZ を選ぶのがおすすめです。

移植するときの書き換え例はこうです。

created_at TIMESTAMPTZ NOT NULL
SQL

そして、INSERT時には NOW() を使う。

INSERT INTO users (email, name, is_active, created_at)
VALUES ('a@example.com', 'Taro', true, NOW());
SQL

移植の基本方針はこうです。

DATETIME → TIMESTAMPTZ（または TIMESTAMP）

「時間として計算できる値」を前提にした設計に、ここで切り替えます。

差分ポイント4：制約の“真面目さ”を前提にする

「CHECK・UNIQUE・外部キーを“ちゃんと効くもの”として書き直す」

MySQLでは、CHECK制約が無視されたり、外部キーがストレージエンジン依存だったりと、
「書いても効かないことがある」世界でした。

PostgreSQLでは、Day5で見たように、制約はかなり真面目に守られます。
移植するときは、「せっかくなら制約も強化する」という発想を持つと良いです。

例えば、users.email のUNIQUEは、MySQLではこうでした。

UNIQUE KEY idx_users_email (email)
SQL

PostgreSQLでは、テーブル定義の中にこう書けます。

email TEXT NOT NULL UNIQUE
SQL

あるいは、後からこう追加してもOKです。

CREATE UNIQUE INDEX idx_users_email ON users(email);
SQL

外部キーも、MySQLではこうでした。

CONSTRAINT fk_posts_user
  FOREIGN KEY (user_id) REFERENCES users(id)
SQL

PostgreSQLでもほぼ同じように書けますが、「ちゃんと効く」前提で設計できます。

user_id INTEGER NOT NULL REFERENCES users(id)
SQL

ここでの重要ポイントは、「制約は“飾り”ではなく、“仕様そのもの”」という意識です。
移植のタイミングで、「CHECKを足す」「UNIQUEを明示する」「外部キーを整理する」といった“設計のアップデート”を一緒にやると、DBの質が一段上がります。

差分ポイント5：インデックスの書き方と“意味づけ”

「“とりあえずKEY”から、“どのクエリを速くしたいか”へ」

MySQLでは、インデックスをこう書いていました。

UNIQUE KEY idx_users_email (email)
KEY idx_posts_user_id (user_id)
SQL

PostgreSQLでは、インデックスは基本的に CREATE INDEX で書きます。

CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
SQL

テーブル定義の中に UNIQUE を書くこともできますが、
「どのインデックスが何のためにあるか」を意識して名前を付けるのが大事です。

移植のタイミングで、「このインデックスはどのクエリを速くするためにあるのか？」を一度考え直すと、
不要なインデックスを減らしたり、足りないインデックスを追加したりできます。

Day6でやったように、「WHEREに頻繁に出てくるカラム」にだけ張る、という基準をここで適用します。

MySQL版をPostgreSQL版に“まとめて書き換えた形”

「差分を全部反映したPostgreSQL版を眺めてみる」

ここまでの差分を全部反映したPostgreSQL版を、一度まとめて眺めてみましょう。

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT        NOT NULL UNIQUE,
  name       VARCHAR(50) NOT NULL,
  is_active  BOOLEAN     NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER      NOT NULL REFERENCES users(id),
  title      VARCHAR(200) NOT NULL,
  body       TEXT         NOT NULL,
  created_at TIMESTAMPTZ  NOT NULL
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
SQL

ここには、Day1〜Day6でやってきた差分が全部乗っています。

SERIAL（シーケンスベースの自動採番）
BOOLEAN（true/falseのフラグ）
TIMESTAMPTZ（時間として計算できる日時）
UNIQUE制約（メールアドレスの一意性）
外部キー（usersとpostsの親子関係）
インデックス（user_idでの検索を速くする）

Day7 前半では、「MySQL版を見て、こういうPostgreSQL版に頭の中で変換できる」状態を目指します。

Day7 前半のまとめ

復習課題としての「MySQLで作ったDBをPostgreSQLに移植する」では、INT UNSIGNED＋AUTO_INCREMENTを SERIAL や INTEGER GENERATED ... AS IDENTITY に置き換え、TINYINT(1) のフラグを BOOLEAN に、DATETIME を TIMESTAMPTZ（または TIMESTAMP）に変えることで、「型・自動採番・時間の扱い」をPostgreSQL流にアップデートする。
さらに、メールアドレスのUNIQUEやユーザーと記事の外部キーを「ちゃんと効く制約」として書き直し、CREATE INDEX ... ON posts(user_id) のように「どのクエリを速くしたいか」を意識してインデックスを張ることで、MySQL版を単にコピペするのではなく、「PostgreSQLの真面目さと差分理解を活かした“ひとつ上の設計”」として移植する――これが Day7 前半で目指す着地点になる。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました