┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL 数据类型                        │
├─────────────────────────────────────────────────────────────┤
│  数值类型    │  字符串类型  │  日期时间类型  │  布尔类型      │
│  JSON 类型   │  数组类型    │  范围类型      │  网络地址类型  │
│  几何类型    │  位串类型    │  枚举类型      │  UUID 类型     │
│  XML 类型    │  文本搜索    │  复合类型      │  范围类型      │
└─────────────────────────────────────────────────────────────┘

-- 创建带自增主键的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 等价的 BIGSERIAL
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_amount DECIMAL(12, 2) DEFAULT 0
);

-- 创建表
CREATE TABLE measurements (
    id SERIAL PRIMARY KEY,
    temperature REAL,          -- 单精度
    humidity DOUBLE PRECISION, -- 双精度
    price NUMERIC(10, 2),       -- 10位数字，2位小数
    amount DECIMAL(15, 4)       -- 15位数字，4位小数
);

-- 插入示例
INSERT INTO measurements (temperature, humidity, price, amount)
VALUES (23.5, 65.789, 199.99, 12345.6789);

-- 创建自定义序列
CREATE SEQUENCE my_sequence START 100 INCREMENT 10;

-- 使用序列
SELECT nextval('my_sequence');  -- 返回 100
SELECT nextval('my_sequence');  -- 返回 110

-- 重置序列
SELECT setval('my_sequence', 100);

-- 在表中使用序列
CREATE TABLE order_items (
    item_id BIGINT DEFAULT nextval('order_seq'),
    order_id BIGINT NOT NULL,
    product_name VARCHAR(100)
);

-- 创建表
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username CHAR(20),      -- 定长 20
    nickname VARCHAR(50),   -- 最多 50 字符
    bio TEXT                 -- 无限制
);

-- 插入示例
INSERT INTO user_profiles (username, nickname, bio)
VALUES 
    ('alice', 'Alice Smith', 'Hello, I am a developer!'),
    ('bob', 'Bob Jones', NULL);

-- 拼接字符串
SELECT 'Hello' || ' ' || 'World';  -- Hello World
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS('-', '2024', '01', '15');  -- 2024-01-15

-- 字符串长度
SELECT LENGTH('Hello');              -- 5
SELECT CHAR_LENGTH('你好');          -- 2

-- 大小写转换
SELECT UPPER('hello');  -- HELLO
SELECT LOWER('HELLO');  -- hello
SELECT INITCAP('hello world');  -- Hello World

-- 去除空格
SELECT TRIM('  hello  ');        -- hello
SELECT LTRIM('  hello  ');       -- hello  
SELECT RTRIM('  hello  ');       --   hello

-- 截取字符串
SELECT SUBSTRING('Hello World', 1, 5);  -- Hello
SELECT SUBSTR('Hello World', 7);         -- World

-- 替换
SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- Hello PostgreSQL

-- 拆分
SELECT STRING_TO_ARRAY('a,b,c', ',');    -- {a,b,c}
SELECT ARRAY_TO_STRING(ARRAY['a','b','c'], ',');  -- a,b,c

-- 创建表
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    start_at TIMESTAMP,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    duration INTERVAL
);

-- 插入示例
INSERT INTO events (event_name, event_date, start_time, start_at, duration)
VALUES 
    ('Conference', '2024-06-15', '09:00:00', '2024-06-15 09:00:00', '3 hours'),
    ('Meeting', '2024-06-16', '14:30:00', '2024-06-16 14:30:00+08', '30 minutes');

-- 当前时间
SELECT CURRENT_DATE;           -- 当前日期
SELECT CURRENT_TIME;           -- 当前时间
SELECT CURRENT_TIMESTAMP;      -- 当前时间戳
SELECT NOW();                  -- 当前时间戳（含时区）
SELECT clock_timestamp();      -- 实时时间戳

-- 日期提取
SELECT EXTRACT(YEAR FROM CURRENT_DATE);   -- 年
SELECT EXTRACT(MONTH FROM CURRENT_DATE);  -- 月
SELECT EXTRACT(DAY FROM CURRENT_DATE);    -- 日
SELECT EXTRACT(DOW FROM CURRENT_DATE);    -- 星期 (0=周日)
SELECT EXTRACT(DOY FROM CURRENT_DATE);     -- 一年中的第几天

-- 日期计算
SELECT CURRENT_DATE + INTERVAL '7 days';   -- 7天后
SELECT CURRENT_DATE - INTERVAL '1 month';   -- 1月前
SELECT AGE('2024-06-15', '2024-01-01');    -- 时间差

-- 格式化
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');           -- 2024-06-15
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(EXTRACT(YEAR FROM CURRENT_DATE), '9999');

-- 字符串转日期
SELECT '2024-06-15'::DATE;
SELECT TO_DATE('15/06/2024', 'DD/MM/YYYY');

-- 字符串转时间戳
SELECT '2024-06-15 10:30:00'::TIMESTAMP;

-- 日期转字符串
SELECT TO_CHAR(CURRENT_DATE, 'YYYY年MM月DD日');
SELECT TO_CHAR(CURRENT_DATE, 'Day, Month DD, YYYY');

-- 时间戳转日期
SELECT CURRENT_TIMESTAMP::DATE;
SELECT DATE_TRUNC('month', CURRENT_DATE);  -- 月初
SELECT DATE_TRUNC('week', CURRENT_DATE);    -- 周初

-- 布尔值字面量
TRUE  |  't'  |  'true'  |  'y'  |  'yes'  |  '1'
FALSE |  'f'  |  'false' |  'n'  |  'no'   |  '0'

-- 创建表
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    is_completed BOOLEAN DEFAULT false,
    is_urgent BOOLEAN DEFAULT false
);

-- 查询示例
SELECT * FROM tasks WHERE is_completed = true;
SELECT * FROM tasks WHERE NOT is_completed;
SELECT 
    task_name,
    CASE WHEN is_completed THEN '✓' ELSE '✗' END AS status
FROM tasks;

-- 创建表
CREATE TABLE api_logs (
    id SERIAL PRIMARY KEY,
    endpoint VARCHAR(100),
    request_data JSON,
    response_data JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入 JSON 数据
INSERT INTO api_logs (endpoint, request_data, response_data)
VALUES 
    ('/api/users',
     '{"user_id": 1, "action": "login"}',
     '{"status": "success", "token": "abc123"}'::JSONB),
    ('/api/products',
     '{"category": "electronics", "limit": 10}',
     '{"products": [{"id": 1, "name": "Laptop"}]}');

-- 获取 JSON 对象字段（返回文本）
SELECT response_data->>'status' FROM api_logs;

-- 获取 JSON 对象字段（返回 JSON）
SELECT response_data->'user' FROM api_logs;

-- 获取嵌套字段
SELECT request_data->'user'->>'name' FROM api_logs;

-- 检查是否包含
SELECT * FROM api_logs WHERE request_data @> '{"user_id": 1}';
SELECT * FROM api_logs WHERE response_data ? 'status';

-- JSON 对象创建
SELECT json_build_object('name', 'John', 'age', 30);
SELECT json_build_array('a', 'b', 'c');

-- JSON 数组展开
SELECT json_array_elements_text('["a", "b", "c"]');

-- JSON 对象键值对展开
SELECT * FROM json_each('{"name": "John", "age": 30}');

-- JSON 路径查询
SELECT * FROM api_logs 
WHERE response_data @@ '$.status == "success"';

-- JSON 路径存在检查
SELECT * FROM api_logs 
WHERE response_data @? '$.products[*] ? (@.id == 1)';

-- 行转 JSON
SELECT json_build_object('id', id, 'name', name)
FROM users WHERE id = 1;

-- JSON 合并
SELECT jsonb_merge('{"a": 1}'::JSONB, '{"b": 2}'::JSONB);

-- 创建 GIN 索引
CREATE INDEX idx_api_logs_response ON api_logs USING GIN (response_data);

-- 创建表达式索引
CREATE INDEX idx_api_logs_status 
ON api_logs ((response_data->>'status')) WHERE response_data ? 'status';

-- 数组类型定义
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    skills TEXT[],              -- 文本数组
    phone_numbers VARCHAR(20)[], -- 定长数组
    scores INTEGER[3]            -- 3个整数的数组
);

-- 插入数组
INSERT INTO employees (name, skills, scores)
VALUES 
    ('张三', ARRAY['Python', 'PostgreSQL', 'Docker'], ARRAY[85, 90, 78]),
    ('李四', '{"Java", "Spring", "MySQL"}', '{90, 85, 92}');

-- 访问数组元素（下标从 1 开始）
SELECT skills[1] FROM employees WHERE name = '张三';

-- 数组切片
SELECT scores[1:2] FROM employees WHERE name = '张三';

-- 数组包含
SELECT * FROM employees WHERE skills @> ARRAY['Python'];
SELECT * FROM employees WHERE skills && ARRAY['Java', 'Go'];

-- 数组长度
SELECT array_length(skills, 1) FROM employees;

-- 展开数组
SELECT id, unnest(skills) AS skill FROM employees;

-- 数组追加
SELECT array_append(ARRAY[1, 2], 3);  -- {1, 2, 3}

-- 数组连接
SELECT array_cat(ARRAY[1, 2], ARRAY[3, 4]);  -- {1, 2, 3, 4}

-- 创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE weekdays AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

-- 使用枚举
CREATE TABLE employee_status (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    current_mood mood,
    work_day weekdays
);

-- 插入枚举值
INSERT INTO employee_status (name, current_mood, work_day)
VALUES 
    ('张三', 'happy', 'Mon'),
    ('李四', 'ok', 'Tue');

-- 枚举函数
SELECT enum_first(NULL::weekdays);   -- Mon
SELECT enum_last(NULL::weekdays);     -- Sun
SELECT enum_range(NULL::weekdays);   -- {Mon,Tue,Wed,Thu,Fri,Sat,Sun}

-- 安装 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 创建表
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER,
    token VARCHAR(255),
    expires_at TIMESTAMPTZ
);

-- 生成 UUID
SELECT uuid_generate_v4();           -- 随机 UUID
SELECT uuid_generate_v1();           -- 基于时间的 UUID
SELECT uuid_generate_v1mc();          -- 改进的 v1 UUID
SELECT uuid_generate_v3('namespace', 'name');  -- 命名空间 UUID

-- 常用 UUID 生成
INSERT INTO sessions (user_id, token, expires_at)
VALUES (1, gen_random_uuid()::TEXT, NOW() + INTERVAL '7 days');

-- 安装网络扩展
CREATE EXTENSION IF NOT EXISTS "ip4r";

-- CIDR 类型
CREATE TABLE networks (
    id SERIAL PRIMARY KEY,
    network_name VARCHAR(50),
    ip_range CIDR
);

-- IPv4 和 IPv6
CREATE TABLE hosts (
    id SERIAL PRIMARY KEY,
    hostname VARCHAR(100),
    ip INET
);

-- 插入数据
INSERT INTO hosts (hostname, ip)
VALUES 
    ('server1', '192.168.1.100'),
    ('server2', '10.0.0.50/24'),
    ('ipv6_server', '2001:db8::1');

-- 内置范围类型
-- int4range, int8range, numrange, tsrange, tstzrange, daterange

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_number INTEGER,
    stay_period DATERANGE,
    price_per_night NUMERIC(10, 2)
);

-- 插入范围数据
INSERT INTO reservations (room_number, stay_period, price_per_night)
VALUES 
    (101, '[2024-06-15, 2024-06-18)', 299.00),
    (102, '[2024-06-20, 2024-06-22)', 350.00);

-- 范围查询
SELECT * FROM reservations 
WHERE stay_period @> '2024-06-16'::DATE;

SELECT * FROM reservations 
WHERE stay_period && '[2024-06-14, 2024-06-16)'::DATERANGE;

-- 创建域类型
CREATE DOMAIN positive_integer 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 DOMAIN us_postal_code AS CHAR(5) 
    CHECK (VALUE ~ '^\d{5}$');

-- 使用域类型
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age positive_integer,
    email email_address,
    zip_code us_postal_code
);