-- 状态字段用 SMALLINT
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status SMALLINT NOT NULL DEFAULT 0  -- 0:待支付 1:已支付 2:已发货 3:已完成
);

-- 不要用 INTEGER 存状态（浪费空间）
-- 不要用 TEXT 存状态（性能差、无法做数值比较）

-- NUMERIC(精度, 小数位)
-- 金额必须用 NUMERIC，不要用 FLOAT！
CREATE TABLE financial (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(15, 2),   -- 最大 999,999,999,999.99
    rate NUMERIC(8, 6),      -- 最大 99.999999
    quantity NUMERIC(10, 0)  -- 整数，但不受 INTEGER 范围限制
);

-- FLOAT 的精度问题（为什么不用 FLOAT 存金额）
SELECT 0.1::FLOAT + 0.2::FLOAT;        -- 0.30000000000000004 ❌
SELECT 0.1::NUMERIC + 0.2::NUMERIC;    -- 0.3 ✅

-- 方式 1：SERIAL（传统方式）
CREATE TABLE t1 (id SERIAL PRIMARY KEY);
-- 等价于创建序列 + 设置默认值
-- 问题：可以直接 INSERT 指定 id 值，绕过序列

-- 方式 2：GENERATED ALWAYS AS IDENTITY（SQL 标准，推荐）
CREATE TABLE t2 (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
-- 不允许手动指定 id 值（除非使用 OVERRIDING SYSTEM VALUE）
INSERT INTO t2 (id) VALUES (1);  -- ❌ ERROR: cannot insert into column "id"
INSERT INTO t2 OVERRIDING SYSTEM VALUE VALUES (1);  -- ✅ 强制插入

-- 方式 3：GENERATED BY DEFAULT AS IDENTITY
CREATE TABLE t3 (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);
-- 允许手动指定，不指定时自动生成

-- 迁移建议：新表用 IDENTITY，旧表逐步迁移

-- 推荐：直接用 TEXT
CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,        -- 不需要 VARCHAR(200)
    content TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL   -- 约束放在 UNIQUE 上，不在类型上
);

-- 如果需要长度约束，用 CHECK
ALTER TABLE articles ADD CONSTRAINT chk_title_len
    CHECK (length(title) BETWEEN 1 AND 200);

SELECT
    length('Hello 你好') AS len,              -- 8（字符数）
    bit_length('Hello') AS bits,               -- 40（位数）
    upper('hello') AS caps,                    -- HELLO
    lower('HELLO') AS low,                     -- hello
    initcap('hello world') AS title,           -- Hello World
    trim('  hello  ') AS trimmed,              -- hello
    ltrim('  hello') AS left_trimmed,          -- hello
    rtrim('hello  ') AS right_trimmed,         -- hello
    concat('Hello', ' ', 'World') AS joined,   -- Hello World
    'Hello' || ' ' || 'World' AS concat2,      -- Hello World
    left('Hello World', 5) AS left5,           -- Hello
    right('Hello World', 5) AS right5,         -- World
    substring('Hello World' from 1 for 5) AS sub, -- Hello
    replace('Hello World', 'World', 'PG') AS replaced, -- Hello PG
    split_part('a,b,c', ',', 2) AS split,      -- b
    repeat('ab', 3) AS repeated,               -- ababab
    reverse('Hello') AS reversed;              -- olleH

CREATE TABLE flags (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 布尔字面量
SELECT TRUE, FALSE, NULL::BOOLEAN;

-- TRUE 值: TRUE, 't', 'true', 'y', 'yes', 'on', '1'
-- FALSE 值: FALSE, 'f', 'false', 'n', 'no', 'off', '0'

-- 布尔聚合
SELECT bool_and(is_active) FROM users;   -- 所有都是 TRUE 则 TRUE
SELECT bool_or(is_active) FROM users;    -- 任一为 TRUE 则 TRUE
SELECT every(is_active) FROM users;      -- bool_and 的别名

-- TIMESTAMPTZ 存储的是 UTC 时间，显示时根据会话时区转换
SET timezone = 'Asia/Shanghai';

SELECT '2024-01-15 14:30:00+08'::TIMESTAMPTZ;
-- 存储: UTC 2024-01-15 06:30:00
-- 显示: 2024-01-15 14:30:00+08

SET timezone = 'America/New_York';
SELECT '2024-01-15 14:30:00+08'::TIMESTAMPTZ;
-- 显示: 2024-01-15 01:30:00-05

SELECT
    now(),                                              -- 当前时间（带时区）
    current_date,                                       -- 当前日期
    current_time,                                       -- 当前时间（带时区）
    clock_timestamp(),                                  -- 实时时钟（每行不同）
    date_trunc('day', now()),                           -- 截断到天
    date_trunc('month', now()),                         -- 截断到月
    date_trunc('year', now()),                          -- 截断到年
    extract(YEAR FROM now()),                           -- 提取年
    extract(MONTH FROM now()),                          -- 提取月
    extract(DOW FROM now()),                            -- 星期几（0=周日）
    extract(EPOCH FROM now()),                          -- Unix 时间戳
    age('2024-01-15'::DATE, '1990-06-01'::DATE),       -- 年龄间隔
    to_char(now(), 'YYYY-MM-DD HH24:MI:SS'),           -- 格式化
    to_timestamp(1700000000),                           -- Unix 时间戳转时间
    '2024-01-15'::DATE + INTERVAL '30 days',            -- 日期加减
    '2024-12-31'::DATE - '2024-01-01'::DATE;            -- 日期差（天数）

-- 查看当前时区
SHOW timezone;

-- 设置时区
SET timezone = 'Asia/Shanghai';

-- 转换时区
SELECT
    now() AT TIME ZONE 'UTC' AS utc_time,
    now() AT TIME ZONE 'Asia/Shanghai' AS shanghai_time,
    now() AT TIME ZONE 'America/New_York' AS ny_time;

-- 从无时区转换为带时区
SELECT '2024-01-15 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai';

-- 创建数组列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}',        -- 文本数组
    scores INTEGER[],                -- 整数数组
    prices NUMERIC(10,2)[]           -- 数值数组
);

-- 插入数组
INSERT INTO products (name, tags, scores) VALUES
    ('Laptop', ARRAY['electronics', 'computers'], ARRAY[85, 90, 78]),
    ('Phone', '{"mobile", "electronics"}', '{95, 88, 92}'),  -- 字面量语法
    ('Book', '{"education", "science"}', '{80, 85}');

-- 数组索引访问（1-based）
SELECT name, tags[1] AS first_tag, scores[2] AS second_score FROM products;

-- 数组操作
SELECT
    array_length(tags, 1) AS tag_count,      -- 数组长度
    array_append(tags, 'new') AS appended,   -- 追加元素
    array_remove(tags, 'electronics') AS removed,  -- 移除元素
    array_cat(ARRAY[1,2], ARRAY[3,4]) AS concatenated, -- 拼接
    array_position(tags, 'electronics') AS pos,  -- 元素位置
    unnest(tags) AS tag                        -- 展开为行
FROM products;

-- 数组查询
-- @> 包含
SELECT * FROM products WHERE tags @> ARRAY['electronics'];
-- <@ 被包含
SELECT * FROM products WHERE ARRAY['electronics'] <@ tags;
-- && 重叠
SELECT * FROM products WHERE tags && ARRAY['electronics', 'books'];
-- ANY
SELECT * FROM products WHERE 'electronics' = ANY(tags);

-- 数组索引
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- Range 字面量
SELECT '[2024-01-01, 2024-12-31]'::DATERANGE;    -- 闭区间
SELECT '[2024-01-01, 2024-12-31)'::DATERANGE;    -- 左闭右开
SELECT '(0, 100]'::INT4RANGE;                     -- 左开右闭

-- 创建使用 Range 的表
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    during TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)  -- 排他约束！
);

INSERT INTO reservations (room_id, during) VALUES
    (1, '[2024-03-15 14:00, 2024-03-15 18:00)'),
    (1, '[2024-03-16 09:00, 2024-03-16 12:00)');

-- 尝试插入重叠会失败（排他约束）
INSERT INTO reservations (room_id, during) VALUES
    (1, '[2024-03-15 16:00, 2024-03-15 20:00)');
-- ERROR: conflicting key value violates exclusion constraint

-- Range 操作
SELECT
    lower(during) AS start_time,
    upper(during) AS end_time,
    isempty(during) AS is_empty,
    lower_inc(during) AS lower_inclusive,
    upper_inc(during) AS upper_inclusive
FROM reservations;

-- Range 包含/重叠判断
SELECT * FROM reservations
WHERE during @> '2024-03-15 15:00'::TIMESTAMPTZ;  -- 包含某时刻

-- 时间范围生成
SELECT * FROM reservations
WHERE during && '[2024-03-15, 2024-03-17]'::DATERANGE;  -- 重叠

-- 复合类型
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code VARCHAR(10)
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    billing_address address,
    shipping_address address
);

-- 使用复合类型
INSERT INTO customers (name, billing_address) VALUES
    ('Alice', ROW('123 Main St', 'Shanghai', 'SH', '200000'));

-- 访问复合类型字段
SELECT
    name,
    (billing_address).city AS city,
    (billing_address).zip_code AS zip
FROM customers;

-- 枚举类型
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral', 'angry');

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT,
    current_mood mood DEFAULT 'neutral'
);

INSERT INTO person (name, current_mood) VALUES ('Bob', 'happy');

-- 向枚举添加新值
ALTER TYPE mood ADD VALUE 'excited' BEFORE 'neutral';

-- 域类型（基于现有类型添加约束）
CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    email email_address NOT NULL,
    login_count positive_int DEFAULT 1
);

CREATE TABLE access_logs (
    id BIGSERIAL PRIMARY KEY,
    ip INET NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO access_logs (ip) VALUES
    ('192.168.1.100'), ('10.0.0.1'), ('::1');

-- 子网查询
SELECT * FROM access_logs WHERE ip <<= '192.168.1.0/24';

-- 需要扩展生成 UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,  -- UUID v4
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]') RETURNING id;
-- 返回类似: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11