PostgreSQLの型・テーブル設計・制約 | PostgreSQL入門

代表的な型を選ぶ

整数にはinteger、正確な金額にはnumeric、文章にはtext、日付にはdate、タイムゾーンを考慮する日時にはtimestamptzを使います。型は後から変更しにくいため、値の範囲と計算方法を先に考えます。

型を確認するSQL

SELECT
    pg_typeof(42) AS integer_type,
    pg_typeof(3.14::numeric) AS numeric_type,
    pg_typeof(now()) AS timestamp_type;

実行結果の例OUTPUT

integer_type  numeric_type  timestamp_type
integer       numeric       timestamp with time zone

キーと制約

主キーは行を一意に識別し、外部キーは関連先の存在を守ります。UNIQUEは重複を禁止し、CHECKは値の範囲を制限します。

顧客テーブルSQL

CREATE TABLE customers (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    name text NOT NULL,
    age integer NOT NULL,
    CONSTRAINT uq_customers_email UNIQUE (email),
    CONSTRAINT chk_customers_age CHECK (age >= 0 AND age <= 150)
);

重複を試すSQL

INSERT INTO customers (email, name, age)
VALUES ('a@example.com', 'Aさん', 20);

INSERT INTO customers (email, name, age)
VALUES ('a@example.com', '別の人', 30);

実行結果OUTPUT

ERROR:  duplicate key value violates unique constraint "uq_customers_email"
DETAIL:  Key (email)=(a@example.com) already exists.

CHECK制約のエラーOUTPUT

INSERT INTO customers (email, name, age)
VALUES ('b@example.com', 'Bさん', 151);
ERROR:  new row for relation "customers" violates check constraint "chk_customers_age"
DETAIL:  Failing row contains (..., 151).

注文と明細へ分ける

注文全体と、注文に含まれる商品の明細は別の表へ分けます。注文時点の価格を明細へ保存すると、商品の現在価格が変わっても過去の注文金額を再現できます。

注文詳細テーブルSQL

CREATE TABLE order_items (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(id),
    product_id integer NOT NULL REFERENCES products(id),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(10, 2) NOT NULL CHECK (unit_price >= 0)
);

テーブルを分ける目的は、表を細かくすることではなく、同じ事実を複数箇所へ保存して矛盾する状態を減らすことです。

JSONBを使う

形が変わりやすい補足情報にはjsonbが候補になります。ただし、すべてをJSONBへ入れると制約や結合が弱くなるため、検索・集計する中心データは通常の列へ設計します。

JSONBと検索SQL

CREATE TABLE product_metadata (
    product_id integer PRIMARY KEY REFERENCES products(id),
    metadata jsonb NOT NULL
);

INSERT INTO product_metadata VALUES
    (1, '{"color": "blue", "tags": ["book", "database"]}');

SELECT product_id, metadata ->> 'color' AS color
FROM product_metadata
WHERE metadata @> '{"tags": ["database"]}';

実行結果OUTPUT

product_id  color
1            blue

インデックスとEXPLAIN

検索条件や結合に使う列へインデックスを作ると、候補行を探しやすくなる場合があります。追加・更新の負担もあるため、実際のデータ量とクエリーで確認します。

インデックスと実行計画SQL

CREATE INDEX idx_products_category
    ON products (category);

EXPLAIN (COSTS, FORMAT TEXT)
SELECT name, price
FROM products
WHERE category = 'book';

EXPLAIN ANALYZEは実際にクエリーを実行します。更新系SQLへ使うとデータを変更することがあるため、練習用DBで実行し、意味を理解してから使いましょう。

PostgreSQL入門の講座一覧

代表的な型を選ぶ

キーと制約

注文と明細へ分ける

JSONBを使う

インデックスとEXPLAIN