PostgreSQLデータ型完全ガイド - 適切な型選択の基準

1
2
3
4
5
6
7

-- 整数型の使用例
CREATE TABLE users (
    user_id      INTEGER PRIMARY KEY,
    age          SMALLINT,          -- 年齢は16ビットで十分
    login_count  INTEGER,           -- 一般的なカウンター
    total_points BIGINT             -- 大きな値になる可能性がある累計
);

1
2
3

-- 型の範囲を超えるとエラーになる
INSERT INTO users (user_id, age) VALUES (1, 40000);
-- ERROR: smallint out of range

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- NUMERIC(精度, スケール) の形式で指定
-- 精度: 全体の桁数、スケール: 小数点以下の桁数
CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        NUMERIC(10, 2) NOT NULL,  -- 最大99,999,999.99
    tax_rate     NUMERIC(5, 4),            -- 0.0000 〜 9.9999
    weight_kg    NUMERIC(8, 3)             -- 99,999.999 まで
);

INSERT INTO products (name, price, tax_rate, weight_kg)
VALUES ('ノートPC', 129800.00, 0.1000, 1.850);

-- 金額計算は正確に行われる
SELECT name, price, price * tax_rate AS tax, price * (1 + tax_rate) AS total
FROM products;

1
2
3
4
5
6

-- 浮動小数点の近似誤差の例
SELECT 0.1::REAL + 0.2::REAL;
-- 結果: 0.30000001192092896（厳密な0.3ではない）

SELECT 0.1::NUMERIC + 0.2::NUMERIC;
-- 結果: 0.3（正確）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- SERIALの使用例
CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    order_date DATE NOT NULL
);

-- 自動的に連番が振られる
INSERT INTO orders (order_date) VALUES ('2026-01-03');
INSERT INTO orders (order_date) VALUES ('2026-01-04');

SELECT * FROM orders;
-- order_id | order_date
-- ---------+------------
--        1 | 2026-01-03
--        2 | 2026-01-04

-- SERIAL は以下と等価
-- CREATE SEQUENCE orders_order_id_seq;
-- CREATE TABLE orders (
--     order_id INTEGER NOT NULL DEFAULT nextval('orders_order_id_seq'),
--     ...
-- );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE TABLE string_examples (
    id          SERIAL PRIMARY KEY,
    code        CHAR(5),        -- 固定長5文字（短ければ空白でパディング）
    username    VARCHAR(50),    -- 最大50文字の可変長
    description TEXT            -- 長さ制限なし
);

INSERT INTO string_examples (code, username, description)
VALUES ('ABC', 'tanaka', 'これは説明文です。長さに制限はありません。');

-- CHAR型は空白でパディングされる
SELECT code, LENGTH(code), username, LENGTH(username)
FROM string_examples;
-- code  | length | username | length
-- ------+--------+----------+--------
-- ABC   |      5 | tanaka   |      6

1
2
3
4

-- 以下の3つはパフォーマンス面でほぼ同等
column_a VARCHAR(100)
column_b TEXT
column_c VARCHAR    -- 長さ指定なし = TEXT と同等

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 実践的なテーブル設計例
CREATE TABLE members (
    member_id     SERIAL PRIMARY KEY,
    email         VARCHAR(254) NOT NULL UNIQUE,
    username      VARCHAR(50) NOT NULL,
    display_name  VARCHAR(100),
    bio           TEXT,
    country_code  CHAR(2),
    phone         VARCHAR(20)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- タイムゾーンの違いを確認
SET timezone = 'Asia/Tokyo';

CREATE TABLE timestamp_test (
    id           SERIAL PRIMARY KEY,
    without_tz   TIMESTAMP,
    with_tz      TIMESTAMP WITH TIME ZONE
);

INSERT INTO timestamp_test (without_tz, with_tz)
VALUES ('2026-01-03 15:00:00', '2026-01-03 15:00:00');

SELECT * FROM timestamp_test;
-- without_tz           | with_tz
-- ---------------------+------------------------
-- 2026-01-03 15:00:00  | 2026-01-03 15:00:00+09

-- タイムゾーンを変更して再度確認
SET timezone = 'UTC';
SELECT * FROM timestamp_test;
-- without_tz           | with_tz
-- ---------------------+------------------------
-- 2026-01-03 15:00:00  | 2026-01-03 06:00:00+00

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- DATE型の基本操作
CREATE TABLE events (
    event_id    SERIAL PRIMARY KEY,
    event_name  VARCHAR(100) NOT NULL,
    event_date  DATE NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES ('新年会', '2026-01-15');

-- 日付の計算
SELECT event_name,
       event_date,
       event_date - CURRENT_DATE AS days_until,
       event_date + INTERVAL '1 week' AS next_week
FROM events;

-- 日付の比較
SELECT * FROM events
WHERE event_date BETWEEN '2026-01-01' AND '2026-01-31';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- INTERVAL型の基本操作
SELECT CURRENT_TIMESTAMP + INTERVAL '1 day' AS tomorrow;
SELECT CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes' AS later;
SELECT CURRENT_TIMESTAMP - INTERVAL '1 month' AS last_month;

-- 年齢計算の例
CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    birth_date    DATE NOT NULL,
    hire_date     DATE NOT NULL
);

INSERT INTO employees (name, birth_date, hire_date)
VALUES ('田中太郎', '1990-05-15', '2015-04-01');

SELECT name,
       birth_date,
       AGE(birth_date) AS age,
       AGE(hire_date) AS tenure
FROM employees;
-- name     | birth_date | age                | tenure
-- ---------+------------+--------------------+-----------------
-- 田中太郎 | 1990-05-15 | 35 years 7 mons... | 10 years 9 mons...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE tasks (
    task_id      SERIAL PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    is_completed BOOLEAN DEFAULT FALSE,
    is_urgent    BOOLEAN DEFAULT FALSE
);

INSERT INTO tasks (title, is_completed, is_urgent) VALUES
('レポート作成', FALSE, TRUE),
('メール返信', TRUE, FALSE),
('会議準備', FALSE, FALSE);

-- 真偽値での検索
SELECT * FROM tasks WHERE is_completed = TRUE;
SELECT * FROM tasks WHERE is_completed;  -- 上と同じ
SELECT * FROM tasks WHERE NOT is_completed;
SELECT * FROM tasks WHERE is_urgent AND NOT is_completed;

1
2
3
4

SELECT TRUE, FALSE;
-- bool | bool
-- -----+------
-- t    | f

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- UUIDの生成と使用（PostgreSQL 13以降はgen_random_uuid()が標準で使用可能）
CREATE TABLE api_keys (
    key_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      INTEGER NOT NULL,
    key_name     VARCHAR(100),
    created_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO api_keys (user_id, key_name)
VALUES (1, 'Production API Key');

SELECT * FROM api_keys;
-- key_id                               | user_id | key_name
-- -------------------------------------+---------+---------------------
-- a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | 1       | Production API Key

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 配列型のテーブル作成
CREATE TABLE articles (
    article_id   SERIAL PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    tags         TEXT[],                    -- 文字列の配列
    scores       INTEGER[]                  -- 整数の配列
);

-- 配列データの挿入
INSERT INTO articles (title, tags, scores) VALUES
('PostgreSQL入門', ARRAY['PostgreSQL', 'データベース', 'SQL'], ARRAY[85, 92, 78]),
('Python活用術', '{"Python", "プログラミング"}', '{90, 88, 95}');

-- 配列要素へのアクセス（1始まりのインデックス）
SELECT title, tags[1] AS first_tag, scores[1] AS first_score
FROM articles;

-- 配列のスライス
SELECT title, tags[1:2] AS first_two_tags
FROM articles;

1
2
3
4
5
6
7
8

-- 特定の要素を含む配列を検索
SELECT * FROM articles WHERE 'PostgreSQL' = ANY(tags);

-- 配列に特定の要素がすべて含まれるか
SELECT * FROM articles WHERE tags @> ARRAY['PostgreSQL', 'SQL'];

-- 配列が重複しているか（共通要素があるか）
SELECT * FROM articles WHERE tags && ARRAY['Python', 'SQL'];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 配列への要素追加
UPDATE articles
SET tags = array_append(tags, '入門')
WHERE article_id = 1;

-- 配列から要素削除
UPDATE articles
SET tags = array_remove(tags, '入門')
WHERE article_id = 1;

-- 配列の長さ
SELECT title, array_length(tags, 1) AS tag_count FROM articles;

-- 配列を行に展開
SELECT title, unnest(tags) AS tag FROM articles;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 配列より正規化が適切なケース
-- NG: 記事とタグの多対多関係を配列で表現
CREATE TABLE articles_bad (
    article_id SERIAL PRIMARY KEY,
    title      VARCHAR(200),
    tags       TEXT[]
);

-- OK: 中間テーブルで正規化
CREATE TABLE articles_good (
    article_id SERIAL PRIMARY KEY,
    title      VARCHAR(200)
);

CREATE TABLE tags (
    tag_id   SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE
);

CREATE TABLE article_tags (
    article_id INTEGER REFERENCES articles_good(article_id),
    tag_id     INTEGER REFERENCES tags(tag_id),
    PRIMARY KEY (article_id, tag_id)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE user_profiles (
    user_id   SERIAL PRIMARY KEY,
    username  VARCHAR(50) NOT NULL,
    profile   JSONB
);

INSERT INTO user_profiles (username, profile) VALUES
('tanaka', '{"age": 30, "city": "Tokyo", "skills": ["Python", "SQL"], "social": {"twitter": "@tanaka", "github": "tanaka-dev"}}'),
('yamada', '{"age": 25, "city": "Osaka", "skills": ["JavaScript", "React"]}');

-- 値の取得（->はJSONB、->>はテキストを返す）
SELECT username,
       profile -> 'age' AS age_json,
       profile ->> 'age' AS age_text,
       profile -> 'social' ->> 'twitter' AS twitter
FROM user_profiles;

-- 配列要素へのアクセス
SELECT username, profile -> 'skills' -> 0 AS first_skill
FROM user_profiles;

-- キーの存在チェック
SELECT * FROM user_profiles WHERE profile ? 'social';

-- 値での検索
SELECT * FROM user_profiles WHERE profile ->> 'city' = 'Tokyo';
SELECT * FROM user_profiles WHERE (profile ->> 'age')::INTEGER >= 28;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- キーの追加・更新
UPDATE user_profiles
SET profile = profile || '{"verified": true}'::JSONB
WHERE username = 'tanaka';

-- ネストしたキーの更新
UPDATE user_profiles
SET profile = jsonb_set(profile, '{social, linkedin}', '"tanaka-pro"')
WHERE username = 'tanaka';

-- キーの削除
UPDATE user_profiles
SET profile = profile - 'verified'
WHERE username = 'tanaka';

-- ネストしたキーの削除
UPDATE user_profiles
SET profile = profile #- '{social, twitter}'
WHERE username = 'tanaka';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- GINインデックスの作成
CREATE INDEX idx_profile ON user_profiles USING GIN (profile);

-- 包含演算子による検索（インデックスが効く）
SELECT * FROM user_profiles
WHERE profile @> '{"city": "Tokyo"}';

-- 配列内の検索（インデックスが効く）
SELECT * FROM user_profiles
WHERE profile -> 'skills' ? 'Python';

-- 特定のパスに対するインデックス
CREATE INDEX idx_profile_city ON user_profiles USING BTREE ((profile ->> 'city'));

SELECT * FROM user_profiles WHERE profile ->> 'city' = 'Tokyo';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- 実践的なテーブル設計例
CREATE TABLE customers (
    -- 主キー: 外部公開するならUUID、内部のみならSERIAL
    customer_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- 文字列: 適切な長さ制限
    email          VARCHAR(254) NOT NULL UNIQUE,
    name           VARCHAR(100) NOT NULL,
    
    -- 電話番号: 国際番号対応
    phone          VARCHAR(20),
    
    -- 住所: 長さ不定
    address        TEXT,
    
    -- 金額: 正確な計算が必要
    credit_limit   NUMERIC(12, 2) DEFAULT 0,
    
    -- フラグ: 真偽値
    is_active      BOOLEAN DEFAULT TRUE,
    is_verified    BOOLEAN DEFAULT FALSE,
    
    -- 日時: タイムゾーン対応
    registered_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login_at  TIMESTAMP WITH TIME ZONE,
    
    -- 日付のみ
    birth_date     DATE,
    
    -- 柔軟なメタデータ
    preferences    JSONB DEFAULT '{}'::JSONB,
    
    -- 単純なリスト（検索頻度が低い場合）
    tags           TEXT[]
);

-- インデックス
CREATE INDEX idx_customers_email ON customers (email);
CREATE INDEX idx_customers_active ON customers (is_active) WHERE is_active = TRUE;
CREATE INDEX idx_customers_preferences ON customers USING GIN (preferences);