一、数据类型概述

PostgreSQL 拥有业界最丰富的数据类型系统，不仅支持标准的 SQL 数据类型，还提供了大量高级数据类型和扩展能力。

1. 数据类型分类总览

PostgreSQL 数据类型体系
├── 数值类型
│   ├── 整数类型（SMALLINT, INTEGER, BIGINT）
│   ├── 浮点类型（REAL, DOUBLE PRECISION）
│   ├── 精确数值（NUMERIC, DECIMAL）
│   └── 序列类型（SMALLSERIAL, SERIAL, BIGSERIAL）
├── 字符类型
│   ├── CHAR(n), VARCHAR(n)
│   └── TEXT
├── 日期时间类型
│   ├── DATE, TIME
│   ├── TIMESTAMP, TIMESTAMPTZ
│   └── INTERVAL
├── 布尔类型
├── 二进制类型
│   └── BYTEA
├── 网络地址类型
│   ├── INET, CIDR
│   └── MACADDR
├── JSON/JSONB
├── 数组类型
├── 范围类型
├── 几何类型
├── 枚举类型
├── 复合类型
├── XML 类型
├── UUID 类型
├── 全文搜索类型
│   └── TSVECTOR, TSQUERY
└── 自定义类型

二、数值类型

1. 整数类型

-- 整数类型示例
CREATE TABLE integer_demo (
    id INTEGER PRIMARY KEY,                    -- 常规主键
    age SMALLINT CHECK (age BETWEEN 0 AND 150), -- 年龄
    population BIGINT,                         -- 人口数量
    score SMALLINT DEFAULT 0,                  -- 评分（0-100）
    visitor_count INTEGER NOT NULL             -- 访问量
);

-- 自动溢出检查
-- SELECT 32767::SMALLINT + 1;  -- 会报错：数值超出范围

-- 使用类型转换
SELECT '123'::INTEGER;
SELECT 123.45::INTEGER;  -- 截断小数，结果为 123

2. 浮点类型

-- 浮点类型示例
CREATE TABLE float_demo (
    price REAL,                           -- 商品价格
    latitude DOUBLE PRECISION,            -- 纬度（需要高精度）
    longitude DOUBLE PRECISION,           -- 经度
    ratio REAL DEFAULT 0.0
);

-- 浮点数注意事项
SELECT 0.1::REAL + 0.2::REAL = 0.3::REAL;  -- 可能为 false（浮点精度问题）

-- 推荐使用精确比较
SELECT ABS((0.1::REAL + 0.2::REAL) - 0.3::REAL) < 0.00001;

-- 特殊值
SELECT 
    'Infinity'::REAL,    -- 正无穷
    '-Infinity'::REAL,   -- 负无穷
    'NaN'::REAL;         -- 非数字

3. 精确数值类型（NUMERIC/DECIMAL）

-- NUMERIC(p, s) 语法
-- p: 总位数（精度），最大 1000
-- s: 小数位数（刻度）

CREATE TABLE numeric_demo (
    amount NUMERIC(10, 2),        -- 总10位，小数2位（如：12345678.90）
    tax_rate NUMERIC(5, 4),       -- 总5位，小数4位（如：0.1825）
    balance NUMERIC,              -- 无精度限制
    price DECIMAL(8, 2)          -- DECIMAL 是 NUMERIC 的同义词
);

-- 插入数据
INSERT INTO numeric_demo VALUES 
    (12345678.90, 0.1825, 1000.50, 99.99),
    (99999999.99, 0.9999, 9999999999.99, 0.01);

-- 数值计算
SELECT 
    amount,
    amount * tax_rate AS tax_amount,
    amount + amount * tax_rate AS total_with_tax
FROM numeric_demo;

-- 四舍五入
SELECT 
    ROUND(123.4567, 2) AS round_2,      -- 123.46
    TRUNC(123.4567, 2) AS trunc_2,      -- 123.45
    CEIL(123.01) AS ceil_value,         -- 124
    FLOOR(123.99) AS floor_value;       -- 123

4. 序列类型（SERIAL）

-- SERIAL 本质是 INTEGER + SEQUENCE
CREATE TABLE serial_demo (
    id SERIAL PRIMARY KEY,          -- 等价于下面的写法
    name TEXT
);

-- 等价写法
CREATE SEQUENCE serial_demo_id_seq;
CREATE TABLE serial_demo (
    id INTEGER NOT NULL DEFAULT nextval('serial_demo_id_seq'),
    name TEXT,
    PRIMARY KEY (id)
);
ALTER SEQUENCE serial_demo_id_seq OWNED BY serial_demo.id;

-- 序列控制
SELECT currval('serial_demo_id_seq');     -- 当前值
SELECT nextval('serial_demo_id_seq');     -- 下一个值
SELECT setval('serial_demo_id_seq', 1000, false);  -- 设置起始值

-- 插入时覆盖序列值
INSERT INTO serial_demo (id, name) VALUES (999, 'special');
INSERT INTO serial_demo (name) VALUES ('auto_id');  -- 自动生成 1000

-- 类型对照表
-- SMALLSERIAL : 2字节，最大 32,767
-- SERIAL      : 4字节，最大 2,147,483,647
-- BIGSERIAL   : 8字节，最大 9,223,372,036,854,775,807

三、字符类型

-- 字符类型对比
CREATE TABLE string_demo (
    fixed CHAR(10),           -- 定长，自动补空格
    variable VARCHAR(100),    -- 变长，有长度限制
    unlimited TEXT,           -- 变长，无长度限制（推荐）
    name VARCHAR(50) NOT NULL
);

-- 插入示例
INSERT INTO string_demo VALUES 
    ('hi', 'hello', 'This is a very long text...', 'Alice'),
    ('1234567890', 'short', '短文本', 'Bob');

-- CHAR 类型的行为（自动补空格）
SELECT 
    fixed,
    LENGTH(fixed) AS fixed_len,           -- 返回实际字符数
    OCTET_LENGTH(fixed) AS fixed_bytes,   -- 包含尾部空格
    CHAR_LENGTH(fixed) AS fixed_chars,    -- 不包含尾部空格
    fixed = 'hi' AS eq_hi,                -- true（尾部空格被忽略）
    fixed = 'hi  ' AS eq_hi_spaces        -- true
FROM string_demo WHERE fixed = 'hi';

-- TEXT 类型常用操作
SELECT 
    LENGTH('Hello World') AS len,                    -- 11
    POSITION('World' IN 'Hello World') AS pos,       -- 7
    SUBSTRING('Hello World' FROM 1 FOR 5) AS sub,    -- Hello
    REPLACE('Hello World', 'World', 'PostgreSQL') AS replaced,
    CONCAT('Hello', ' ', 'World') AS concatenated,
    UPPER('hello') AS upper_case,
    LOWER('HELLO') AS lower_case,
    INITCAP('hello world') AS title_case,
    REVERSE('hello') AS reversed,
    LEFT('Hello World', 5) AS left_part,
    RIGHT('Hello World', 5) AS right_part;

-- 字符串模式匹配
SELECT 
    'PostgreSQL' LIKE 'Post%',           -- true
    'PostgreSQL' ILIKE 'post%',          -- true（大小写不敏感）
    'PostgreSQL' SIMILAR TO 'Post%SQL',  -- true
    'PostgreSQL' ~ '^Post.*SQL$';        -- true（正则）

-- 字符串编码处理
SELECT 
    convert('Hello', 'UTF8', 'LATIN1'),
    encode('Hello'::BYTEA, 'base64'),
    decode('SGVsbG8=', 'base64');

-- 字符串聚合
SELECT 
    STRING_AGG(username, ', ' ORDER BY id) AS user_list
FROM users;

四、日期时间类型

1. 类型详解

-- 创建时间类型表
CREATE TABLE datetime_demo (
    event_date DATE,
    start_time TIME,
    start_time_tz TIME WITH TIME ZONE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    duration INTERVAL,
    -- 带精度的类型
    precise_time TIME(3),        -- 毫秒精度
    precise_ts TIMESTAMP(6)      -- 微秒精度
);

-- 插入当前时间
INSERT INTO datetime_demo (
    event_date, 
    start_time, 
    start_time_tz, 
    created_at, 
    updated_at, 
    duration
) VALUES (
    CURRENT_DATE,                -- 2024-01-15
    CURRENT_TIME,                -- 14:30:25.123456
    CURRENT_TIME AT TIME ZONE 'UTC',
    CURRENT_TIMESTAMP,           -- 2024-01-15 14:30:25.123456
    NOW(),                       -- 同 CURRENT_TIMESTAMP
    INTERVAL '1 day 2 hours'     -- 1 day 02:00:00
);

-- 时间计算
SELECT 
    event_date,
    event_date + 30 AS date_plus_30_days,
    event_date + INTERVAL '1 month' AS date_plus_month,
    created_at + duration AS completion_time,
    AGE(CURRENT_DATE, event_date) AS days_ago,
    EXTRACT(EPOCH FROM duration) AS duration_seconds
FROM datetime_demo;

2. 时区处理

-- 查看当前时区设置
SHOW timezone;                    -- Asia/Shanghai（或 UTC）

-- 设置会话时区
SET timezone = 'UTC';
SET timezone = 'America/New_York';
SET timezone = 'Asia/Tokyo';

-- 时区转换
SELECT 
    NOW() AS local_time,
    NOW() AT TIME ZONE 'UTC' AS utc_time,
    NOW() AT TIME ZONE 'EST' AS est_time,
    NOW() AT TIME ZONE 'Asia/Shanghai' AS shanghai_time;

-- 时间戳带时区转换
SELECT 
    '2024-01-15 14:30:00+08'::TIMESTAMPTZ,
    '2024-01-15 14:30:00+08'::TIMESTAMPTZ AT TIME ZONE 'UTC',
    '2024-01-15 06:30:00 UTC'::TIMESTAMPTZ AT TIME ZONE 'Asia/Shanghai';

-- 查看有效时区列表
SELECT name FROM pg_timezone_names WHERE name LIKE 'Asia/%' ORDER BY name;

3. 时间函数大全

-- 日期截断
SELECT 
    DATE_TRUNC('year', NOW()) AS year_start,
    DATE_TRUNC('quarter', NOW()) AS quarter_start,
    DATE_TRUNC('month', NOW()) AS month_start,
    DATE_TRUNC('week', NOW()) AS week_start,
    DATE_TRUNC('day', NOW()) AS day_start,
    DATE_TRUNC('hour', NOW()) AS hour_start,
    DATE_TRUNC('minute', NOW()) AS minute_start;

-- 日期提取
SELECT 
    EXTRACT(YEAR FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY FROM NOW()) AS day,
    EXTRACT(DOW FROM NOW()) AS day_of_week,      -- 0=周日, 1=周一
    EXTRACT(ISODOW FROM NOW()) AS iso_day,       -- 1=周一, 7=周日
    EXTRACT(WEEK FROM NOW()) AS week,
    EXTRACT(DOY FROM NOW()) AS day_of_year,
    EXTRACT(QUARTER FROM NOW()) AS quarter,
    EXTRACT(EPOCH FROM NOW()) AS unix_timestamp;

-- 日期加减
SELECT 
    NOW() + INTERVAL '1 day' AS tomorrow,
    NOW() - INTERVAL '3 months' AS three_months_ago,
    NOW() + '1 year 2 months 3 days'::INTERVAL,
    NOW() - '2024-01-01'::TIMESTAMP AS days_since_newyear;

-- 年龄计算
SELECT 
    AGE('2024-12-31', '1990-06-15') AS age,
    DATE_PART('year', AGE('2024-12-31', '1990-06-15')) AS years;

-- 生成日期序列
SELECT generate_series(
    '2024-01-01'::DATE,
    '2024-12-31'::DATE,
    '1 month'::INTERVAL
) AS month_start;

-- 判断日期范围
SELECT 
    NOW() BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024,
    NOW()::DATE = CURRENT_DATE AS is_today,
    NOW()::DATE = CURRENT_DATE - 1 AS is_yesterday;

五、布尔类型

-- 布尔类型使用
CREATE TABLE boolean_demo (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN
);

-- 插入数据
INSERT INTO boolean_demo (is_active, is_deleted, is_verified) VALUES
    (TRUE, FALSE, TRUE),
    ('yes', 'no', 'on'),    -- 'yes','on','1','t' 都表示 TRUE
    ('no', 'yes', 'off'),   -- 'no','off','0','f' 都表示 FALSE
    (NULL, NULL, NULL);

-- 布尔查询
SELECT * FROM boolean_demo WHERE is_active = TRUE;
SELECT * FROM boolean_demo WHERE is_active;           -- 简洁写法
SELECT * FROM boolean_demo WHERE NOT is_deleted;
SELECT * FROM boolean_demo WHERE is_verified IS NULL;

-- 布尔类型转换
SELECT 
    TRUE::INTEGER,          -- 1
    FALSE::INTEGER,         -- 0
    1::BOOLEAN,             -- true
    0::BOOLEAN,             -- false
    't'::BOOLEAN,           -- true
    'f'::BOOLEAN;           -- false

-- 布尔聚合
SELECT 
    BOOL_AND(is_active) AS all_active,      -- 所有都为真才为真
    BOOL_OR(is_active) AS any_active,       -- 任一为真即为真
    EVERY(is_active) AS every_active        -- 同 BOOL_AND
FROM boolean_demo;

六、二进制类型（BYTEA）

-- BYTEA 类型使用
CREATE TABLE binary_demo (
    id SERIAL PRIMARY KEY,
    image_data BYTEA,
    hash BYTEA,
    metadata BYTEA
);

-- 插入十六进制数据
INSERT INTO binary_demo (image_data) VALUES 
    ('\xDEADBEEF'::BYTEA),                    -- 十六进制格式
    (E'\\xDEADBEEF'::BYTEA),
    (decode('DEADBEEF', 'hex'));              -- 从文本转换

-- 编码转换
SELECT 
    image_data,
    ENCODE(image_data, 'base64') AS base64,
    ENCODE(image_data, 'hex') AS hex,
    ENCODE(image_data, 'escape') AS escaped
FROM binary_demo;

-- 二进制操作
SELECT 
    '\xDEADBEEF'::BYTEA || '\x12345678'::BYTEA AS concatenated,
    OCTET_LENGTH('\xDEADBEEF'::BYTEA) AS length,
    GET_BYTE('\xDEADBEEF'::BYTEA, 1) AS second_byte;

七、JSON/JSONB 类型

-- JSON 与 JSONB 对比
-- JSON: 存储原始文本，输入快，输出慢
-- JSONB: 存储二进制，输入慢（需解析），输出快，支持索引

CREATE TABLE json_demo (
    id SERIAL PRIMARY KEY,
    data_json JSON,
    data_jsonb JSONB,
    config JSONB DEFAULT '{"theme": "light", "language": "zh"}'::JSONB
);

-- 插入 JSON 数据
INSERT INTO json_demo (data_json, data_jsonb) VALUES
('{"name": "Alice", "age": 30}', '{"name": "Alice", "age": 30}'),
(
    '{"products": [{"id": 1, "name": "Laptop"}, {"id": 2, "name": "Mouse"}]}',
    '{"products": [{"id": 1, "name": "Laptop"}, {"id": 2, "name": "Mouse"}]}'
);

-- JSONB 操作符
SELECT 
    data_jsonb,
    data_jsonb -> 'name' AS name_json,           -- 返回 JSON 对象
    data_jsonb ->> 'name' AS name_text,          -- 返回文本
    data_jsonb -> 'products' -> 0 -> 'name' AS first_product_name,
    data_jsonb #>> '{products,0,name}' AS same_result
FROM json_demo;

-- JSONB 包含和存在检查
SELECT * FROM json_demo
WHERE data_jsonb @> '{"name": "Alice"}';         -- 包含检查

SELECT * FROM json_demo
WHERE data_jsonb ? 'name';                       -- 键存在

-- JSONB 更新（只适用于 JSONB）
UPDATE json_demo 
SET data_jsonb = data_jsonb || '{"age": 31, "city": "Beijing"}'::JSONB
WHERE data_jsonb ->> 'name' = 'Alice';

-- JSONB 删除键
UPDATE json_demo 
SET data_jsonb = data_jsonb - 'age' - 'city'
WHERE data_jsonb ->> 'name' = 'Alice';

-- 创建 GIN 索引
CREATE INDEX idx_jsonb_data ON json_demo USING gin(data_jsonb);

-- JSONB 聚合
SELECT 
    jsonb_agg(data_jsonb) AS all_data,
    jsonb_object_agg(data_jsonb->>'name', data_jsonb->'age') AS name_age_map
FROM json_demo;

八、数组类型

-- 数组类型示例
CREATE TABLE array_demo (
    id SERIAL PRIMARY KEY,
    tags TEXT[],                          -- 一维文本数组
    scores INTEGER[],                     -- 整数数组
    matrix INTEGER[][],                   -- 二维数组
    colors VARCHAR(20)[],                 -- 带长度约束的数组
    data BYTEA[]                          -- 数组内可以是任意类型
);

-- 插入数组数据
INSERT INTO array_demo (tags, scores, matrix) VALUES
    (ARRAY['sql', 'postgresql', 'database'], ARRAY[95, 87, 92], ARRAY[[1,2],[3,4]]),
    ('{"python", "django", "api"}'::TEXT[], '{88, 90, 85}'::INTEGER[], '{{5,6},{7,8}}'),
    (ARRAY['testing'], ARRAY[75], NULL);

-- 数组访问
SELECT 
    tags,
    tags[1] AS first_tag,                 -- 索引从1开始
    tags[1:2] AS first_two_tags,          -- 切片
    array_length(tags, 1) AS tag_count,
    cardinality(tags) AS tag_count_alt,   -- 元素总数
    array_ndims(matrix) AS dimensions     -- 维度数
FROM array_demo;

-- 数组操作符
SELECT 
    ARRAY[1,2,3] || ARRAY[4,5,6] AS concatenated,
    ARRAY[1,2,3] || 4 AS append_element,
    ARRAY[1,2,3] && ARRAY[3,4,5] AS overlap,      -- 是否有重叠
    ARRAY[1,2,3] @> ARRAY[2,3] AS contains,       -- 是否包含
    ARRAY[1,2,3] <@ ARRAY[1,2,3,4] AS is_subset;  -- 是否为子集

-- 数组函数
SELECT 
    unnest(ARRAY[1,2,3]) AS element,              -- 展开为行
    array_to_string(ARRAY['a','b','c'], ',') AS joined,
    string_to_array('a,b,c', ',') AS split,
    array_cat(ARRAY[1,2], ARRAY[3,4]) AS cat,
    array_append(ARRAY[1,2], 3) AS append,
    array_prepend(0, ARRAY[1,2]) AS prepend,
    array_remove(ARRAY[1,2,3,2,4], 2) AS removed,
    array_replace(ARRAY[1,2,3,2], 2, 99) AS replaced;

-- 数组搜索
SELECT * FROM array_demo
WHERE 'sql' = ANY(tags);                           -- 任意元素匹配

SELECT * FROM array_demo
WHERE tags @> ARRAY['sql', 'database'];            -- 包含所有标签

-- 数组聚合
SELECT 
    string_agg(tag, ', ') AS tags_joined,
    array_agg(DISTINCT tag) AS unique_tags
FROM array_demo, unnest(tags) AS tag;

九、范围类型

-- 范围类型家族：int4range, int8range, numrange, tsrange, tstzrange, daterange

CREATE TABLE range_demo (
    id SERIAL PRIMARY KEY,
    price_range INT4RANGE,                     -- 整数范围
    valid_period DATERANGE,                    -- 日期范围
    meeting_time TSRANGE,                      -- 时间戳范围
    temperature NUMRANGE,                      -- 数值范围
    -- 带约束的范围列
    active_days DATERANGE NOT NULL
        CHECK (NOT isempty(active_days) AND upper(active_days) IS NOT NULL)
);

-- 插入范围数据
INSERT INTO range_demo (price_range, valid_period, meeting_time, temperature) VALUES
    ('[100, 500]'::INT4RANGE,                 -- 包含边界
     '[2024-01-01, 2024-12-31]'::DATERANGE,
     '[2024-06-01 09:00, 2024-06-01 17:00)'::TSRANGE,  -- 半开区间
     '(0, 100)'::NUMRANGE),                   -- 开区间
  
    (int4range(200, 1000, '[)'),              -- 使用构造函数
     daterange('2024-01-01', '2024-06-30', '[]'),
     tstzrange('2024-06-15 10:00', '2024-06-15 12:00', '[]'),
     numrange(20, 30, '(]'));

-- 范围操作符
SELECT 
    price_range,
    lower(price_range) AS low,                 -- 下界
    upper(price_range) AS high,                -- 上界
    lower_inc(price_range) AS low_inc,         -- 下界是否包含
    upper_inc(price_range) AS high_inc,        -- 上界是否包含
    isempty(price_range) AS empty              -- 是否为空
FROM range_demo;

-- 范围关系判断
SELECT 
    price_range,
    int4range(100, 200) @> price_range,        -- 包含关系
    price_range && int4range(300, 600),        -- 重叠
    price_range << int4range(1000, 2000),      -- 严格左侧
    price_range >> int4range(0, 50)            -- 严格右侧
FROM range_demo;

-- 范围操作
SELECT 
    int4range(100, 500) + int4range(400, 600) AS union_range,       -- 并集
    int4range(100, 500) * int4range(400, 600) AS intersect_range,   -- 交集
    int4range(100, 500) - int4range(200, 300) AS difference_range;  -- 差集

-- 使用范围排除约束（防止时间重叠）
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
    room_id INTEGER,
    time_range TSRANGE,
    EXCLUDE USING gist (room_id WITH =, time_range WITH &&)  -- 同一房间时间不能重叠
);

-- GiST 索引加速范围查询
CREATE INDEX idx_range_demo ON range_demo USING gist(price_range);

十、几何类型

-- PostGIS 是更强大的地理空间解决方案，这里是基础几何类型

CREATE TABLE geometric_demo (
    id SERIAL PRIMARY KEY,
    point POINT,                    -- 点 (x,y)
    line LINE,                      -- 无限直线 {A,B,C}
    lseg LSEG,                      -- 线段 [(x1,y1),(x2,y2)]
    box BOX,                        -- 矩形 (x1,y1),(x2,y2)
    path PATH,                      -- 路径（开放或闭合）
    polygon POLYGON,                -- 多边形
    circle CIRCLE                   -- 圆 <(x,y),r>
);

-- 插入几何数据
INSERT INTO geometric_demo (point, line, lseg, box, polygon, circle) VALUES
    ('(10,10)', '{1,-1,0}', '[(0,0),(10,10)]', '(0,0),(100,100)', 
     '((0,0),(10,0),(10,10),(0,10))', '<(50,50),25>'),
    (point(30,40), line '{2,3,5}', lseg(point(0,0), point(20,20)),
     box(point(0,0), point(50,50)), polygon('(10,10),(20,10),(20,20),(10,20)'),
     circle '<(100,100),50>');

-- 几何操作符和函数
SELECT 
    point(10,10) <-> point(20,20) AS distance,                    -- 欧几里得距离
    point(10,10) @> point(15,15) AS contains,                     -- 包含
    point(10,10) <@ box '(0,0),(100,100)' AS inside_box,          -- 在矩形内
    center(box '(0,0),(100,100)') AS box_center,                  -- 矩形中心
    area(box '(0,0),(100,100)') AS box_area,                      -- 面积
    radius(circle '<(50,50),25>') AS circle_radius;               -- 半径

-- 几何转换
SELECT 
    point(10,10) + point(5,5) AS translation,                     -- 平移
    point(10,10) * point(2,2) AS scaling,                         -- 缩放
    rotate(point(10,0), radians(90)) AS rotated;                  -- 旋转

十一、网络地址类型

-- INET: IPv4/IPv6 地址
-- CIDR: 网络地址（IP地址+子网掩码）
-- MACADDR: MAC 地址

CREATE TABLE network_demo (
    id SERIAL PRIMARY KEY,
    ip_address INET,
    network CIDR,
    mac MACADDR,
    ipv6 INET
);

-- 插入数据
INSERT INTO network_demo (ip_address, network, mac, ipv6) VALUES
    ('192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03', '2001:0db8:85a3:0000:0000:8a2e:0370:7334'),
    ('10.0.0.1/8', '10.0.0.0/8', '08-00-2B-01-02-03', '::1'),
    ('172.16.1.1', '172.16.0.0/12', '08002b:010203', NULL);

-- 网络函数和操作符
SELECT 
    ip_address,
    host(ip_address) AS host_part,               -- 主机部分
    masklen(ip_address) AS mask_length,          -- 子网掩码长度
    broadcast(ip_address) AS broadcast,          -- 广播地址
    network(ip_address) AS network_addr,         -- 网络地址
    family(ip_address) AS ip_family,             -- 4 或 6
    ip_address << '192.168.0.0/16' AS is_subnet, -- 是否在子网内
    ip_address >>= '192.168.1.0/24' AS is_supernet, -- 是否包含子网
    ip_address < '192.168.2.1' AS less_than      -- 比较大小
FROM network_demo;

-- IP 范围查询
SELECT * FROM network_demo 
WHERE ip_address << '192.168.0.0/16';

-- 创建索引
CREATE INDEX idx_network_ip ON network_demo USING gist(ip_address inet_ops);

十二、UUID 类型

-- 启用 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- UUID 类型使用
CREATE TABLE uuid_demo (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),           -- PostgreSQL 13+
    user_id UUID DEFAULT uuid_generate_v4(),                 -- v4 随机 UUID
    namespace_id UUID DEFAULT uuid_generate_v1(),            -- v1 时间+MAC
    url_id UUID DEFAULT uuid_generate_v5('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'example.com')  -- v5 命名空间
);

-- 插入数据
INSERT INTO uuid_demo (id) VALUES (DEFAULT);

-- UUID 函数
SELECT 
    gen_random_uuid() AS random_uuid,
    uuid_generate_v1() AS uuid_v1,
    uuid_generate_v4() AS uuid_v4,
    uuid_generate_v5(uuid_ns_url(), 'https://example.com') AS uuid_v5,
    uuid_nil() AS nil_uuid,                                   -- 全零 UUID
    uuid_ns_dns() AS dns_namespace;                           -- DNS 命名空间

-- UUID 作为主键的注意事项（考虑使用 BRIN 索引）
CREATE INDEX idx_uuid_demo_id ON uuid_demo USING brin(id);

十三、复合类型

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

CREATE TYPE contact_info AS (
    phone VARCHAR(20),
    email VARCHAR(100),
    address address          -- 嵌套复合类型
);

-- 使用复合类型
CREATE TABLE users_compound (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    contact contact_info,
    metadata JSONB
);

-- 插入复合类型数据
INSERT INTO users_compound (name, contact) VALUES (
    'Alice Chen',
    ROW('13800138000', 'alice@example.com', 
        ROW('123 Main St', 'Beijing', 'BJ', '100000', 'China')::address)::contact_info
);

-- 访问复合类型的字段
SELECT 
    name,
    (contact).phone,
    (contact).email,
    (contact).address.city,
    (contact).address.street
FROM users_compound;

-- 更新复合类型字段
UPDATE users_compound 
SET contact.address.city = 'Shanghai'
WHERE name = 'Alice Chen';

十四、枚举类型

-- 创建枚举类型
CREATE TYPE user_status AS ENUM ('active', 'pending', 'suspended', 'deleted');
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE TYPE priority_level AS ENUM ('low', 'medium', 'high', 'urgent');

-- 使用枚举类型
CREATE TABLE enum_demo (
    id SERIAL PRIMARY KEY,
    status user_status DEFAULT 'pending',
    order_state order_status,
    priority priority_level
);

-- 插入数据
INSERT INTO enum_demo (status, order_state, priority) VALUES
    ('active', 'delivered', 'high'),
    ('pending', 'processing', 'medium');

-- 枚举操作
SELECT 
    enum_range(NULL::user_status) AS all_values,
    enum_first(NULL::user_status) AS first_value,
    enum_last(NULL::user_status) AS last_value;

-- 添加枚举值
ALTER TYPE user_status ADD VALUE 'archived' BEFORE 'deleted';
ALTER TYPE user_status ADD VALUE 'locked' AFTER 'suspended';

-- 查看枚举类型
SELECT 
    typname,
    enumlabel,
    enumsortorder
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE typname = 'user_status'
ORDER BY enumsortorder;

十五、XML 类型

-- 创建 XML 类型表
CREATE TABLE xml_demo (
    id SERIAL PRIMARY KEY,
    data XML,
    config XML
);

-- 插入 XML 数据
INSERT INTO xml_demo (data, config) VALUES
    (XMLPARSE(DOCUMENT '<?xml version="1.0"?><book><title>PostgreSQL</title><author>John</author></book>'),
     XMLPARSE(CONTENT '<settings><theme>dark</theme></settings>'));

-- XML 函数
SELECT 
    data,
    xpath('/book/title/text()', data) AS title,
    xpath('/book/author/text()', data) AS author,
    xpath_exists(data, '/book/title') AS has_title
FROM xml_demo;

-- 将查询结果转为 XML
SELECT 
    xmlelement(name "users",
        xmlagg(
            xmlelement(name "user",
                xmlattributes(id AS "id"),
                xmlelement(name "name", username),
                xmlelement(name "email", email)
            )
        )
    ) AS users_xml
FROM users
WHERE id <= 5;

-- 将表转为 XML
SELECT table_to_xml('users', true, false, '');

十六、数据类型选择指南

-- === 数据类型选择建议 ===

-- 1. 整数选择
-- SMALLINT: 年龄、评分（0-100）、状态码
-- INTEGER: 常规ID、普通计数
-- BIGINT: 大数据量ID、时间戳（Unix timestamp）

-- 2. 字符串选择
-- TEXT: 大多数情况推荐，无长度限制
-- VARCHAR(n): 需要强制长度限制的场景（如身份证号、手机号）
-- CHAR(n): 几乎不推荐使用（仅用于固定长度代码）

-- 3. 精确数值 vs 浮点数
-- NUMERIC: 财务、金额计算（要求精度的场景）
-- REAL/DOUBLE: 科学计算、统计数据（允许误差的场景）

-- 4. 时间选择
-- DATE: 仅日期
-- TIMESTAMP: 常规时间记录
-- TIMESTAMPTZ: 多时区应用
-- INTERVAL: 存储时间差

-- 5. 结构化数据
-- JSONB: 灵活的模式、嵌套数据（推荐）
-- JSON: 仅需要原样存储的场景
-- 复合类型: 固定的复杂结构
-- XML: 需要 XML 标准支持

-- 6. 特殊场景
-- UUID: 分布式系统主键
-- INET: IP 地址存储和查询
-- ARRAY: 简单的列表数据
-- RANGE: 区间数据（价格范围、有效期）
-- BYTEA: 小文件、加密数据