PostgreSQL: PostgreSQLデータ型：完全ガイド

-- Eコマースデータベースの正しい型選択
CREATE TABLE smart_products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 将来を見据えたID
    name VARCHAR(200) NOT NULL,                          -- 制限付き文字列
    description TEXT,                                    -- 無制限テキスト
    price DECIMAL(10, 2) NOT NULL,                       -- 正確な金額
    weight_kg REAL,                                      -- 近似値で十分
    is_available BOOLEAN DEFAULT true,                   -- はい/いいえフラグ
    created_at TIMESTAMPTZ DEFAULT NOW(),                -- タイムゾーン対応
    source_ip INET,                                      -- IPアドレス型
    attributes JSONB DEFAULT '{}'::jsonb                 -- 柔軟なスキーマ
);

-- SMALLINT：小範囲の値
CREATE TABLE ratings (
    user_id INTEGER REFERENCES users(id),
    product_id INTEGER REFERENCES products(id),
    score SMALLINT CHECK (score BETWEEN 1 AND 5),  -- 1-5はSMALLINTの範囲内で十分
    PRIMARY KEY (user_id, product_id)
);

-- INTEGER：ほとんどのID
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,  -- SERIAL = INTEGER + 自動採番シーケンス
    name VARCHAR(100) NOT NULL
);

-- BIGINT：高成長テーブル
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,  -- BIGSERIAL = BIGINT + 自動採番
    action VARCHAR(50),
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE

-- SQL標準のIDENTITY列（PostgreSQL 10+）
CREATE TABLE orders_v2 (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(12, 2) DEFAULT 0
);

-- idを指定せずに挿入（自動生成）
INSERT INTO orders_v2 (user_id, total) VALUES (1, 99.99);

-- idを手動で挿入しようとすると失敗（GENERATED ALWAYSの場合）
-- INSERT INTO orders_v2 (id, user_id, total) VALUES (100, 1, 50.00);

-- 時々手動上書きが必要な場合はGENERATED BY DEFAULTを使用
CREATE TABLE logs (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    message TEXT
);

INSERT 0 1

-- 浮動小数点の精度トラップ：0.1 + 0.2 != 0.3
SELECT 0.1::REAL + 0.2::REAL = 0.3::REAL AS float_equal;
-- 出力：f（偽！）

-- DECIMALは精度損失なし
SELECT 0.1::DECIMAL + 0.2::DECIMAL = 0.3::DECIMAL AS decimal_equal;
-- 出力：t（真！）

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- DECIMAL(精度, スケール)
-- 精度 = 総桁数、スケール = 小数点以下の桁数
-- DECIMAL(10,2) = 最大99,999,999.99

CREATE TABLE products_precise (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10, 2) NOT NULL,       -- 最大99,999,999.99
    tax_rate DECIMAL(5, 4) DEFAULT 0.0875, -- 0.0875 = 8.75%
    discount NUMERIC(5, 2) DEFAULT 0.00   -- 最大999.99%
);

-- 金額計算は正確
SELECT name,
       price,
       price * tax_rate AS tax_amount,
       price + (price * tax_rate) AS price_with_tax
FROM products_precise;

 count 
-------
     5
(1 row)

-- CHAR：固定長（スペースでパディング）
SELECT LENGTH('abc'::CHAR(5));
-- 出力：5（5文字にパディング）

-- VARCHAR：制限付き可変長
SELECT LENGTH('abc'::VARCHAR(5));
-- 出力：3（そのまま保存、最大5）

-- TEXT：可変長、制限なし
SELECT LENGTH('abc'::TEXT);
-- 出力：3（制限なし）

-- パフォーマンステスト：PGではVARCHARとTEXTは同一
-- （MySQLのようにVARCHARがTEXTより速いということはない）

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- タイムゾーンをUTCに設定
SET timezone = 'UTC';

-- 異なる型で同じ時刻を挿入
INSERT INTO test_times (ts_no_tz, ts_with_tz) VALUES
    ('2026-07-13 10:00:00', '2026-07-13 10:00:00+00');

-- 東京のタイムゾーンに変更
SET timezone = 'Asia/Tokyo';

-- TIMESTAMP（タイムゾーンなし）は同じリテラルを表示
SELECT ts_no_tz FROM test_times;
-- 出力：2026-07-13 10:00:00（変更なし、どのタイムゾーンか不明！）

-- TIMESTAMPTZはローカルタイムゾーンに変換
SELECT ts_with_tz FROM test_times;
-- 出力：2026-07-13 19:00:00+09（10:00 UTC = 19:00 東京）

INSERT 0 1

-- 現在の日付と時刻
SELECT NOW();                    -- 2026-07-13 10:30:00.123456+00
SELECT CURRENT_DATE;             -- 2026-07-13
SELECT CURRENT_TIMESTAMP;        -- NOW()と同じ

-- INTERVALを使った日付演算
SELECT NOW() + INTERVAL '7 days';     -- 1週間後
SELECT NOW() - INTERVAL '3 months';   -- 3ヶ月前

-- 年齢計算
SELECT AGE(TIMESTAMP '1990-05-15');   -- 36年1ヶ月28日

-- 部分抽出
SELECT EXTRACT(YEAR FROM NOW());      -- 2026
SELECT EXTRACT(MONTH FROM NOW());     -- 7
SELECT EXTRACT(DOW FROM NOW());       -- 1（月曜日、0=日曜日）

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- uuid-ossp拡張を有効化
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 異なるUUIDバージョンを生成
SELECT uuid_generate_v4();  -- ランダムUUID（最も一般的）
SELECT uuid_generate_v1();  -- 時刻ベースUUID

-- INET：単一IPまたはIP範囲
CREATE TABLE access_log (
    id BIGSERIAL PRIMARY KEY,
    source_ip INET NOT NULL,
    access_time TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO access_log (source_ip) VALUES
    ('192.168.1.100'),
    ('10.0.0.5'),
    ('2001:db8::1');

-- クエリ：サブネット内の全IPを検索（VARCHARでは不可能！）
SELECT source_ip FROM access_log
WHERE source_ip << '192.168.1.0/24'::INET;
-- << は「含まれる」を意味する

-- CIDR：ネットワーク範囲
SELECT '192.168.1.0/24'::CIDR;

INSERT 0 1

-- ブーリアンは複数の表現を受け付ける
SELECT true, 't', 'true', 'yes', 'on', '1';   -- すべて = TRUE
SELECT false, 'f', 'false', 'no', 'off', '0';  -- すべて = FALSE

-- WHERE句でのブーリアン
SELECT name FROM products WHERE is_available IS TRUE;
SELECT name FROM products WHERE is_available IS NOT FALSE;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- 列挙型を作成（1回作成すれば複数テーブルで再利用可能）
CREATE TYPE order_status AS ENUM (
    'pending', 'paid', 'shipped', 'delivered', 'cancelled'
);

-- テーブル定義で使用
CREATE TABLE orders_enum (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- 列挙値は自動的に検証される
INSERT INTO orders_enum (status) VALUES ('pending');   -- OK
-- INSERT INTO orders_enum (status) VALUES ('unknown');  -- エラー！

INSERT 0 1

-- ============================================
-- 完全な例：主要な型をすべて使用したテーブル
-- センサーデータ収集システム
-- ============================================

-- 列挙型を作成
CREATE TYPE sensor_status AS ENUM ('active', 'inactive', 'maintenance');

-- 多様なデータ型でテーブルを作成
CREATE TABLE sensors (
    -- 識別
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid UUID DEFAULT uuid_generate_v4() UNIQUE,

    -- テキスト
    name VARCHAR(100) NOT NULL,
    description TEXT,
    location_code CHAR(10),

    -- 数値
    latitude DECIMAL(9, 6) CHECK (latitude BETWEEN -90 AND 90),
    longitude DECIMAL(9, 6) CHECK (longitude BETWEEN -180 AND 180),
    altitude REAL,

    -- ネットワーク
    ip_address INET,
    subnet CIDR,

    -- ステータス
    status sensor_status DEFAULT 'active',
    is_online BOOLEAN DEFAULT false,

    -- 時間
    installed_at DATE,
    last_reading_at TIMESTAMPTZ,
    reading_interval INTERVAL DEFAULT INTERVAL '5 minutes',

    -- 柔軟なデータ
    metadata JSONB DEFAULT '{}'::jsonb,

    -- 監査
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- サンプルデータを挿入
INSERT INTO sensors (name, latitude, longitude, ip_address, status, installed_at, metadata)
VALUES (
    '温度センサー A1',
    35.6762, 139.6503,
    '192.168.1.50',
    'active',
    '2025-01-15',
    '{"model": "TX-200", "unit": "celsius", "range_min": -40, "range_max": 85}'::jsonb
);

-- 型固有の演算子を使ったクエリ
SELECT name, ip_address, metadata->>'model' AS model
FROM sensors
WHERE ip_address << '192.168.1.0/24'::INET
  AND status = 'active';