PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：差分理解 － Day3 データ型の理解

SQL PostgreSQL
スポンサーリンク

Day3 後半のゴール

「“どの型を選ぶか”を意識してテーブル設計できるようになる」

前半で、TEXT / VARCHAR・BOOLEAN・TIMESTAMPのざっくりしたイメージはつかめました。
後半では、「実際のテーブル設計でどう使い分けるか」「MySQLから乗り換えるときにどこを変えるか」を、具体例で深掘りします。

ここでのゴールは、次の状態です。
ユーザー・ブログ・ECなどの簡単なスキーマで、TEXTとVARCHARの役割分担を説明できる。
BOOLEANを使ったフラグ設計と、典型的なWHERE句の書き方をイメージできる。
TIMESTAMPを使った「期間条件」「並び替え」「監査的な使い方」を自分の言葉で語れる。

TEXT / VARCHARを“実際のスキーマ”で使い分ける

ユーザー・ブログ・ECの3パターンで考える

まずは、3つのよくあるテーブルを題材にします。

ユーザー情報
ブログ記事
ECの商品

それぞれで、TEXTとVARCHARをどう分けるかを考えてみます。

ユーザー情報なら、こういう設計が自然です。

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(50)  NOT NULL,
  email      VARCHAR(255) NOT NULL,
  bio        TEXT          NULL
);
SQL

name は「画面に表示する名前」で、あまり長くさせたくないので VARCHAR(50)。
email は「仕様上の上限はあるが、そこまで厳密に気にしない」ので VARCHAR(255)。
bio（自己紹介）は「長くてもいいし、途中で仕様が変わるかもしれない」ので TEXT。

ブログ記事なら、こうなります。

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  body       TEXT         NOT NULL,
  slug       VARCHAR(100) NOT NULL
);
SQL

title は「ある程度の長さ制限をかけたい」ので VARCHAR(200)。
body は「本文」で、長さ制限をあまり気にしないので TEXT。
slug（URL用の短い文字列）は「絶対に短いはず」なので VARCHAR(100)。

ECの商品なら、こういう感じです。

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  description TEXT         NULL,
  sku         VARCHAR(50)  NOT NULL
);
SQL

name は画面表示用で、ある程度の上限を決めたい。
description は「長くてもいい説明文」で、TEXT。
sku（商品コード）は「短い識別子」で、VARCHAR(50)。

ここでの重要な視点は、「TEXTは“何でもあり”、VARCHAR(n)は“ここまで”という設計の意思表示」ということです。
「このカラムはどこまで自由でいいか」を考えて、TEXTかVARCHARかを選ぶ癖をつけると、設計の質が一段上がります。

BOOLEANで“フラグだらけのテーブル”を整理する

フラグを名前と条件式で読みやすくする

次に、BOOLEANを使ったフラグ設計です。
MySQLでは TINYINT(1) で 0/1 を持っていたような場面を、PostgreSQLではBOOLEANに置き換えていきます。

例えば、ユーザーの状態を表すフラグが複数あるケースを考えます。

CREATE TABLE users (
  id             SERIAL PRIMARY KEY,
  name           TEXT    NOT NULL,
  is_active      BOOLEAN NOT NULL,
  is_admin       BOOLEAN NOT NULL,
  email_verified BOOLEAN NOT NULL
);
SQL

ここで、典型的なクエリをいくつか見てみます。

「有効な一般ユーザーだけを取りたい」なら、こう書けます。

SELECT *
FROM users
WHERE is_active = true
  AND is_admin = false;
SQL

あるいは、もっとシンプルに、

SELECT *
FROM users
WHERE is_active
  AND NOT is_admin;
SQL

「メールアドレスが未確認のユーザーを取りたい」なら、

SELECT *
FROM users
WHERE email_verified = false;
SQL

BOOLEANをちゃんと使うと、WHERE句が「仕様書の文章」に近づきます。
WHERE is_active AND NOT is_admin は、「有効で、管理者ではないユーザー」という意味がそのまま伝わります。

MySQL的な WHERE is_active = 1 AND is_admin = 0 から、
PostgreSQL的な WHERE is_active AND NOT is_admin に切り替えるだけで、SQLの読みやすさがかなり変わります。

ここでの深掘りポイントは、「フラグ名を“質問文”にする」ということです。
is_active、is_admin、email_verified のように、「〜か？」で読める名前にすると、BOOLEANとの相性が抜群になります。

TIMESTAMPで“時間を軸にしたクエリ”を組み立てる

期間条件・並び替え・監査の3つの使い方

TIMESTAMPは、「いつ作られたか」「いつ更新されたか」「いつ起きたイベントか」を表す型です。
ここでは、3つの典型的な使い方を押さえます。

1. 期間条件（直近◯日・◯時間）

ユーザーの作成日時を持つテーブルがあるとします。

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       TEXT      NOT NULL,
  created_at TIMESTAMP NOT NULL
);
SQL

「直近7日以内に登録したユーザー」を取りたいときは、こう書きます。

SELECT *
FROM users
WHERE created_at >= NOW() - INTERVAL '7 days';
SQL

「直近24時間以内」なら、

SELECT *
FROM users
WHERE created_at >= NOW() - INTERVAL '24 hours';
SQL

文字列日付ではなくTIMESTAMPで持っているからこそ、
「現在時刻から◯日引いた値」と比較する、という自然な書き方ができます。

2. 並び替え（新しい順・古い順）

TIMESTAMPは、ORDER BYとも相性が良いです。

「新しい順にユーザーを並べたい」なら、

SELECT *
FROM users
ORDER BY created_at DESC;
SQL

「古い順」なら、

SELECT *
FROM users
ORDER BY created_at ASC;
SQL

MySQLでも同じように書けますが、PostgreSQLではTIMESTAMPが標準的な型としてしっかり扱われるので、
「日付を文字列で持っていて、ソートがおかしくなる」といった事故が起きにくくなります。

3. 監査的な使い方（いつ変更されたかを追う）

更新日時を持つテーブルでは、updated_at TIMESTAMP がよく登場します。

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        TEXT      NOT NULL,
  price       INTEGER   NOT NULL,
  updated_at  TIMESTAMP NOT NULL
);
SQL

価格を更新するときに、こう書きます。

UPDATE products
SET price = 1500,
    updated_at = NOW()
WHERE id = 1;
SQL

後から、「この商品はいつ値上げされたのか」を調べたいときに、
updated_at を見れば一発で分かります。

監査ログほど重厚ではないですが、「いつ変わったか」を残しておくことで、
トラブル時の原因調査や、履歴の確認がぐっと楽になります。

ここでの重要ポイントは、「created_at / updated_at は“ほぼ必須の監査情報”としてTIMESTAMPで持つ」という習慣です。
PostgreSQLのTIMESTAMPは、そのための型として非常に頼りになります。

MySQLからPostgreSQLに“型の考え方”を持ち上げる

「とりあえずVARCHAR(255)・TINYINT(1)・DATETIMEからの卒業」

最後に、MySQLからPostgreSQLに移るときの「型のリファクタリング」をイメージしてみます。

MySQLでよくあるパターンは、こういう感じです。

名前やメールアドレス：VARCHAR(255)
フラグ：TINYINT(1)
日時：DATETIME

これをPostgreSQLに持ってくるとき、こう変えると気持ちがいいです。

名前：VARCHAR(50) など、用途に合わせた上限を決める
メールアドレス：VARCHAR(255) のままでもOKだが、「本当に255必要か？」を一度考える
フラグ：BOOLEAN にして、is_active などの名前にする
日時：TIMESTAMP（またはタイムゾーンを意識するなら TIMESTAMPTZ）にする

例えば、ユーザーテーブルならこうなります。

CREATE TABLE users (
  id             SERIAL PRIMARY KEY,
  name           VARCHAR(50)  NOT NULL,
  email          VARCHAR(255) NOT NULL,
  is_active      BOOLEAN      NOT NULL,
  created_at     TIMESTAMPTZ  NOT NULL,
  updated_at     TIMESTAMPTZ  NOT NULL
);
SQL

TIMESTAMPTZ（タイムゾーン付き）を選ぶと、「日本時間とUTCの差」を意識したシステムでも安心して扱えます。
Day3では詳細には踏み込みませんが、「グローバルなサービスならTIMESTAMPTZを選ぶ」という感覚だけ持っておくと良いです。

Day3 後半のまとめ

PostgreSQLでTEXT / VARCHARを使い分けるときは、ユーザー名・タイトル・SKUなど「上限を決めたい文字列」は VARCHAR(n)、自己紹介・本文・説明文など「長さをあまり制限したくないもの」は TEXT とし、「どこまでをDBに守らせるか」という設計の意思を型に乗せる。
BOOLEANは、is_active・is_admin・email_verified のような“質問文”に近いカラム名と組み合わせることで、WHERE is_active AND NOT is_admin のような「仕様書の文章に近い条件式」を書けるようになり、TINYINT(1)＋0/1の世界から卒業できる。
TIMESTAMP（あるいはTIMESTAMPTZ）は、created_at >= NOW() - INTERVAL '7 days' のような期間条件、ORDER BY created_at DESC のような新着順ソート、updated_at = NOW() による更新履歴の記録など、「時間を軸にしたクエリ」と「軽い監査情報」を自然に扱える型であり、MySQLでの DATETIME や文字列日付から、PostgreSQLらしい「時間として計算できる値」を前提にした設計へと、自分のSQLスタイルを一段引き上げるのが Day3 後半の着地点になる。

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