PostgreSQL 数据类型与表设计

一、数据类型概览

PostgreSQL 提供了非常丰富的数据类型系统，这是其区别于其他数据库的重要特性之一。

二、数值类型

2.1 整数类型

类型	存储大小	范围	使用场景
SMALLINT	2 字节	-32768 ~ 32767	小范围整数、状态码
INTEGER	4 字节	-2147483648 ~ 2147483647	常规整数、主键
BIGINT	8 字节	-9223372036854775808 ~ 9223372036854775807	大数值、大表主键

-- 创建表示例
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    status SMALLINT DEFAULT 1,
    view_count BIGINT DEFAULT 0
);

-- 自增序列
CREATE TABLE users (
    id SERIAL PRIMARY KEY,          -- 4 字节自增
    -- 等价于：
    -- id INTEGER DEFAULT nextval('users_id_seq')
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY        -- 8 字节自增（大表推荐）
);

-- 查看序列
SELECT currval('users_id_seq');
SELECT nextval('users_id_seq');
SELECT setval('users_id_seq', 1000);

2.2 精确数值类型

-- NUMERIC(precision, scale)
-- precision: 总位数（最大 1000）
-- scale: 小数位数

CREATE TABLE financial (
    -- 金额：总共12位，小数2位
    amount NUMERIC(12, 2),

    -- 汇率：总共10位，小数6位
    exchange_rate NUMERIC(10, 6),

    -- 无限精度
    precise_value NUMERIC
);

-- 特殊值
SELECT 'NaN'::NUMERIC;        -- 非数字
SELECT 'Infinity'::NUMERIC;   -- 无穷大（不推荐）

-- 计算示例
SELECT 10.5::NUMERIC / 3;     -- 精确计算
SELECT 10.5 / 3;              -- 浮点计算（有精度损失）

2.3 浮点类型

-- REAL: 4 字节，6 位十进制精度
-- DOUBLE PRECISION: 8 字节，15 位十进制精度

CREATE TABLE measurements (
    temperature REAL,
    precise_value DOUBLE PRECISION
);

-- 注意：浮点数有精度问题
SELECT 0.1::REAL + 0.2::REAL;  -- 可能不精确等于 0.3

-- 特殊值
SELECT 'Infinity'::DOUBLE PRECISION;
SELECT '-Infinity'::DOUBLE PRECISION;
SELECT 'NaN'::DOUBLE PRECISION;

三、字符类型

3.1 字符类型对比

类型	描述	最大长度	存储
CHAR(n)	定长，空格填充	10485760	n 字节
VARCHAR(n)	变长，有长度限制	10485760	实际长度 + 1-4 字节
TEXT	变长，无长度限制	无限	实际长度 + 1-4 字节

CREATE TABLE example (
    -- 定长：存储时会填充空格
    code CHAR(10),

    -- 变长：最多 100 个字符
    name VARCHAR(100),

    -- 无限长度（实际限制约 1GB）
    description TEXT
);

-- VARCHAR 和 TEXT 性能基本相同
-- PostgreSQL 内部处理方式相同
-- TEXT 更简洁，推荐使用

-- 字符串操作
SELECT LENGTH('hello');           -- 5
SELECT CHAR_LENGTH('你好');        -- 2
SELECT OCTET_LENGTH('你好');       -- 6 (UTF-8)
SELECT UPPER('hello');            -- HELLO
SELECT LOWER('HELLO');            -- hello
SELECT TRIM('  hello  ');         -- hello
SELECT LEFT('hello', 2);          -- he
SELECT RIGHT('hello', 2);         -- lo
SELECT SUBSTRING('hello' FROM 2 FOR 3);  -- ell

3.2 编码与排序

-- 查看数据库编码
SHOW server_encoding;

-- 查看客户端编码
SHOW client_encoding;

-- 设置客户端编码
SET client_encoding TO 'UTF8';

-- 排序规则
CREATE TABLE chinese_data (
    name TEXT COLLATE "zh_CN.UTF-8"
);

-- 按拼音排序
SELECT * FROM chinese_data ORDER BY name COLLATE "zh_CN.UTF-8";

四、日期时间类型

4.1 类型详解

类型	存储	范围	精度
DATE	4 字节	4713 BC ~ 5874897 AD	天
TIME	8 字节	00:00:00 ~ 24:00:00	微秒
TIMESTAMP	8 字节	4713 BC ~ 294276 AD	微秒
TIMESTAMPTZ	8 字节	4713 BC ~ 294276 AD	微秒
INTERVAL	16 字节	-178000000 年 ~ 178000000 年	微秒

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 带时区的时间戳（推荐）
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    duration INTERVAL
);

-- 插入数据
INSERT INTO events (event_date, start_time, duration)
VALUES ('2024-01-15', '14:30:00', '2 hours 30 minutes');

-- 时间计算
SELECT
    CURRENT_DATE,
    CURRENT_TIME,
    CURRENT_TIMESTAMP,
    NOW(),
    NOW() + INTERVAL '1 day',
    NOW() - INTERVAL '1 month',
    AGE(TIMESTAMP '2000-01-01'),
    DATE_TRUNC('month', NOW())
;

-- 提取日期部分
SELECT
    EXTRACT(YEAR FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY FROM NOW()) AS day,
    EXTRACT(HOUR FROM NOW()) AS hour,
    EXTRACT(DOW FROM NOW()) AS day_of_week,  -- 0=周日
    EXTRACT(EPOCH FROM NOW()) AS unix_timestamp
;

-- 格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日');

-- 解析
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD');
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');

4.2 时区处理

-- 查看时区
SHOW timezone;

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

-- TIMESTAMP vs TIMESTAMPTZ
-- TIMESTAMP: 不存储时区，按字面值存储
-- TIMESTAMPTZ: 存储为 UTC，查询时转换为当前时区

CREATE TABLE time_test (
    ts TIMESTAMP,
    tstz TIMESTAMPTZ
);

SET timezone TO 'Asia/Shanghai';
INSERT INTO time_test VALUES ('2024-01-15 12:00:00', '2024-01-15 12:00:00');

SET timezone TO 'UTC';
SELECT * FROM time_test;
-- ts: 2024-01-15 12:00:00（不变）
-- tstz: 2024-01-15 04:00:00（转换为 UTC）

-- 时区转换
SELECT
    NOW() AT TIME ZONE 'UTC',
    NOW() AT TIME ZONE 'America/New_York',
    NOW() AT TIME ZONE 'Asia/Tokyo'
;

五、JSON 类型

5.1 JSON vs JSONB

特性	JSON	JSONB
存储格式	文本	二进制
写入速度	快	略慢（需解析）
查询速度	慢（需解析）	快
保留格式	是	否
重复键	保留	只保留最后一个
索引支持	不支持	支持 GIN 索引
推荐	只存储	需要查询

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    -- 使用 JSONB（推荐）
    attributes JSONB,
    -- 原始 JSON（很少使用）
    raw_data JSON
);

-- 插入 JSON 数据
INSERT INTO products (name, attributes) VALUES
('iPhone 15', '{"color": "black", "storage": "128GB", "price": 6999}'),
('MacBook Pro', '{"color": "silver", "memory": "16GB", "storage": "512GB"}');

-- 查询 JSON 字段
-- -> 返回 JSON 类型
-- ->> 返回文本类型
SELECT
    name,
    attributes->'color' AS color_json,
    attributes->>'color' AS color_text,
    attributes->>'price' AS price
FROM products;

-- 嵌套查询
SELECT attributes->'specs'->>'cpu' FROM products;

-- 数组查询
SELECT attributes->'tags'->>0 FROM products;  -- 第一个元素

-- 包含查询
SELECT * FROM products WHERE attributes @> '{"color": "black"}';

-- 键存在查询
SELECT * FROM products WHERE attributes ? 'color';
SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];  -- 任一存在
SELECT * FROM products WHERE attributes ?& ARRAY['color', 'storage'];  -- 全部存在

-- 路径查询（PostgreSQL 12+）
SELECT attributes #> '{specs, cpu}' FROM products;
SELECT attributes #>> '{specs, cpu}' FROM products;

-- JSON 路径表达式（PostgreSQL 12+）
SELECT * FROM products
WHERE jsonb_path_exists(attributes, '$.price ? (@ > 5000)');

5.2 JSONB 操作函数

-- 构造 JSON
SELECT jsonb_build_object('name', 'John', 'age', 30);
SELECT jsonb_build_array(1, 2, 3, 'four');

-- 聚合为 JSON
SELECT jsonb_agg(name) FROM products;
SELECT jsonb_object_agg(id, name) FROM products;

-- 展开 JSON
SELECT * FROM jsonb_each('{"a": 1, "b": 2}');
SELECT * FROM jsonb_each_text('{"a": 1, "b": 2}');
SELECT * FROM jsonb_array_elements('[1, 2, 3]');
SELECT * FROM jsonb_array_elements_text('["a", "b", "c"]');

-- 修改 JSON
UPDATE products SET attributes = attributes || '{"stock": 100}' WHERE id = 1;
UPDATE products SET attributes = attributes - 'old_key' WHERE id = 1;
UPDATE products SET attributes = jsonb_set(attributes, '{price}', '7999') WHERE id = 1;

-- 删除键
UPDATE products SET attributes = attributes - 'color';
UPDATE products SET attributes = attributes #- '{nested, key}';

-- 合并
SELECT '{"a": 1}'::jsonb || '{"b": 2}'::jsonb;

-- 深度合并（PostgreSQL 16+）
SELECT jsonb_merge('{"a": {"b": 1}}'::jsonb, '{"a": {"c": 2}}'::jsonb);

5.3 JSONB 索引

-- GIN 索引（推荐）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 支持的操作：@>, ?, ?|, ?&

-- 针对特定路径的索引
CREATE INDEX idx_products_color ON products USING BTREE ((attributes->>'color'));

-- 表达式索引
CREATE INDEX idx_products_price ON products USING BTREE (((attributes->>'price')::INTEGER));

六、数组类型

6.1 数组基础

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[],                    -- 一维数组
    matrix INTEGER[][]              -- 多维数组
);

-- 插入数组
INSERT INTO articles (title, tags) VALUES
('PostgreSQL Guide', ARRAY['database', 'postgresql', 'tutorial']),
('Python Basics', '{"python", "programming", "beginner"}');

-- 查询数组
SELECT tags[1] FROM articles;              -- 第一个元素（1-based）
SELECT tags[1:2] FROM articles;            -- 切片

-- 数组包含
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];  -- 包含
SELECT * FROM articles WHERE tags && ARRAY['python', 'java'];  -- 重叠
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);     -- 任一匹配

-- 数组长度
SELECT array_length(tags, 1) FROM articles;
SELECT cardinality(tags) FROM articles;

-- 数组操作
SELECT array_append(tags, 'new_tag') FROM articles;
SELECT array_prepend('first_tag', tags) FROM articles;
SELECT array_remove(tags, 'python') FROM articles;
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);

-- 展开数组
SELECT unnest(tags) FROM articles;

-- 聚合为数组
SELECT array_agg(DISTINCT tag) FROM (
    SELECT unnest(tags) AS tag FROM articles
) t;

6.2 数组索引

-- GIN 索引支持数组
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 支持的操作：@>, &&, =

七、范围类型

7.1 内置范围类型

类型	描述
INT4RANGE	INTEGER 范围
INT8RANGE	BIGINT 范围
NUMRANGE	NUMERIC 范围
TSRANGE	TIMESTAMP 范围
TSTZRANGE	TIMESTAMPTZ 范围
DATERANGE	DATE 范围

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    -- 预订时间范围
    during TSTZRANGE,
    -- 排除重叠
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- 插入范围
INSERT INTO reservations (room_id, during) VALUES
(1, '[2024-01-15 14:00, 2024-01-15 16:00)'),
(1, '[2024-01-15 16:00, 2024-01-15 18:00)');

-- 范围语法
-- [a, b]: 包含 a 和 b
-- (a, b): 不包含 a 和 b
-- [a, b): 包含 a，不包含 b
-- (a, b]: 不包含 a，包含 b

-- 范围操作符
SELECT
    '[1,5)'::INT4RANGE @> 3,           -- 包含元素
    '[1,5)'::INT4RANGE @> '[2,4)'::INT4RANGE,  -- 包含范围
    '[1,5)'::INT4RANGE && '[3,7)'::INT4RANGE,  -- 重叠
    '[1,5)'::INT4RANGE << '[6,10)'::INT4RANGE, -- 左边
    '[6,10)'::INT4RANGE >> '[1,5)'::INT4RANGE, -- 右边
    '[1,5)'::INT4RANGE -|- '[5,10)'::INT4RANGE -- 相邻
;

-- 范围函数
SELECT
    lower('[1,5)'::INT4RANGE),         -- 1
    upper('[1,5)'::INT4RANGE),         -- 5
    isempty('empty'::INT4RANGE),       -- true
    lower_inc('[1,5)'::INT4RANGE),     -- true
    upper_inc('[1,5)'::INT4RANGE)      -- false
;

-- 范围交集和并集
SELECT
    '[1,5)'::INT4RANGE * '[3,7)'::INT4RANGE,   -- 交集 [3,5)
    '[1,5)'::INT4RANGE + '[3,7)'::INT4RANGE    -- 并集 [1,7)
;

八、特殊类型

8.1 UUID

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

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 生成 UUID
SELECT uuid_generate_v4();  -- 随机 UUID
SELECT uuid_generate_v1();  -- 基于时间的 UUID
SELECT gen_random_uuid();   -- PostgreSQL 13+ 内置

-- UUID 操作
SELECT '550e8400-e29b-41d4-a716-446655440000'::UUID;

8.2 网络地址类型

CREATE TABLE network_logs (
    id SERIAL PRIMARY KEY,
    client_ip INET,
    network CIDR,
    mac_address MACADDR
);

INSERT INTO network_logs (client_ip, network, mac_address) VALUES
('192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03');

-- 网络地址操作
SELECT
    '192.168.1.0/24'::INET >> '192.168.1.100'::INET,  -- 包含
    '192.168.1.100'::INET << '192.168.1.0/24'::INET,  -- 被包含
    masklen('192.168.1.0/24'::INET),                  -- 24
    host('192.168.1.100/24'::INET),                   -- 192.168.1.100
    network('192.168.1.100/24'::INET)                 -- 192.168.1.0/24
;

8.3 枚举类型

-- 创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- 使用枚举
INSERT INTO orders (status) VALUES ('paid');

-- 查询枚举值
SELECT enum_range(NULL::order_status);

-- 添加枚举值
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';

-- 枚举排序（按定义顺序）
SELECT * FROM orders ORDER BY status;

九、表设计最佳实践

9.1 主键设计

-- 方案1：SERIAL（推荐简单场景）
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

-- 方案2：BIGSERIAL（推荐大表）
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY
);

-- 方案3：UUID（分布式系统）
CREATE TABLE distributed_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

-- 方案4：复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

9.2 表设计模板

-- 完整的表设计示例
CREATE TABLE users (
    -- 主键
    id BIGSERIAL PRIMARY KEY,

    -- 基本信息
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,

    -- 状态字段
    status SMALLINT DEFAULT 1,
    is_verified BOOLEAN DEFAULT FALSE,

    -- 扩展信息（JSON）
    profile JSONB DEFAULT '{}',

    -- 时间戳
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,  -- 软删除

    -- 约束
    CONSTRAINT users_username_unique UNIQUE (username),
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_username_length CHECK (LENGTH(username) >= 3),
    CONSTRAINT users_status_valid CHECK (status IN (0, 1, 2))
);

-- 索引
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_status ON users (status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at ON users (created_at DESC);
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '用户ID';
COMMENT ON COLUMN users.status IS '状态：0-禁用，1-正常，2-待审核';

-- 自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

9.3 范式与反范式

-- 第三范式设计（规范化）
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    total_amount NUMERIC(12, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(id),
    product_id BIGINT REFERENCES products(id),
    quantity INTEGER,
    unit_price NUMERIC(10, 2)
);

-- 反范式设计（提高查询性能）
CREATE TABLE orders_denormalized (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    -- 冗余用户信息
    user_name VARCHAR(50),
    user_email VARCHAR(100),
    -- 订单项 JSON
    items JSONB,
    total_amount NUMERIC(12, 2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 使用物化视图实现反范式
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    o.id,
    o.user_id,
    u.username,
    u.email,
    o.total_amount,
    COALESCE(jsonb_agg(jsonb_build_object(
        'product_id', oi.product_id,
        'quantity', oi.quantity,
        'unit_price', oi.unit_price
    )), '[]') AS items
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, u.id;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW order_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;  -- 不锁表

9.4 分区表设计

-- 范围分区（按时间）
CREATE TABLE logs (
    id BIGSERIAL,
    message TEXT,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 默认分区
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

-- 列表分区（按状态）
CREATE TABLE orders_partitioned (
    id BIGSERIAL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ
) PARTITION BY LIST (status);

CREATE TABLE orders_pending PARTITION OF orders_partitioned
    FOR VALUES IN ('pending');
CREATE TABLE orders_completed PARTITION OF orders_partitioned
    FOR VALUES IN ('paid', 'shipped', 'delivered');
CREATE TABLE orders_cancelled PARTITION OF orders_partitioned
    FOR VALUES IN ('cancelled', 'refunded');

-- 哈希分区（均匀分布）
CREATE TABLE data (
    id BIGINT NOT NULL,
    content TEXT
) PARTITION BY HASH (id);

CREATE TABLE data_0 PARTITION OF data FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE data_1 PARTITION OF data FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE data_2 PARTITION OF data FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE data_3 PARTITION OF data FOR VALUES WITH (MODULUS 4, REMAINDER 3);

十、总结

类型选择指南

场景	推荐类型
主键	BIGSERIAL 或 UUID
金额	NUMERIC(12, 2)
状态	SMALLINT 或 ENUM
字符串	TEXT（无长度限制时）或 VARCHAR(n)
时间戳	TIMESTAMPTZ
扩展属性	JSONB
标签/分类	TEXT[] 或 JSONB
时间范围	TSTZRANGE
IP 地址	INET
布尔值	BOOLEAN

表设计原则

1. 始终使用主键：BIGSERIAL 或 UUID
2. 添加时间戳：created_at, updated_at
3. 使用合适类型：不要用 VARCHAR 存数字
4. 添加注释：COMMENT ON 记录字段含义
5. 建立索引：根据查询模式建立
6. 考虑分区：大表使用分区提高性能
7. 适度反范式：在性能需要时使用 JSONB