PostgreSQL 数据类型（千字长文）

为什么理解 PostgreSQL 数据类型是数据库开发的第一步

在开始写 SQL 查询之前，你是否曾思考过：为什么数据库要区分“整数”和“小数”？为什么一个字段能存“文本”，另一个却只能存“日期”？这些看似基础的问题，其实都指向同一个核心概念——PostgreSQL 数据类型。

如果你正在使用 PostgreSQL 构建应用，无论是博客系统、电商后台还是数据分析平台，掌握数据类型就像掌握建房子的砖块。每种类型都有其独特的“尺寸”和“用途”，选错了，轻则导致性能下降，重则引发数据异常甚至系统崩溃。

想象一下，如果你用“整数”类型去存银行账户余额，结果因为精度丢失导致金额变成 100.0000000000000001 元，这可是要出大事的。而如果用“字符串”存日期，后续做时间范围查询时，数据库会把“2024-01-01”当成“2024-01-02”来比较，因为字符串比较是按字典序来的。

所以，理解 PostgreSQL 数据类型，不是为了背下几十种类型的名字，而是为了在真实业务场景中，做出正确选择。接下来，我们就从最基础的几类开始，一步步拆解。

数值类型：从整数到高精度计算

PostgreSQL 提供了多种数值类型，满足从简单计数到复杂财务计算的不同需求。

整数类型：从小到大的数字容器

-- 创建一个用户表，使用 integer 存储用户 ID
CREATE TABLE users (
    user_id integer PRIMARY KEY,  -- 主键，用于唯一标识用户
    age smallint,                 -- 年龄一般不会超过 150，用 smallint 足够
    score bigint                  -- 游戏积分可能很高，用 bigint 防溢出
);

注意：虽然 integer 是默认选择，但如果你知道某个字段的值永远不超过 100，用 smallint 更节省空间。数据库的存储效率，往往就藏在这些细节里。

浮点与定点类型：精度之争

当你要处理货币、科学计算或汇率时，选择“浮点数”还是“定点数”至关重要。

• real：单精度浮点，约 6 位有效数字，适合图像处理、物理模拟等对精度要求不高的场景。
• double precision：双精度浮点，约 15 位有效数字，更精确，但仍可能有舍入误差。
• numeric（或 decimal）：推荐用于财务数据，支持任意精度，不会丢失小数位。

-- 创建一个商品价格表，使用 numeric 保证精度
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    name varchar(100),
    price numeric(10, 2) NOT NULL  -- 10位数字，2位小数，如 999.99
);

-- 插入数据
INSERT INTO products (name, price) VALUES 
    ('iPhone 15', 5999.99),
    ('MacBook Air', 8999.00);

-- 查询时不会出现精度误差
SELECT name, price FROM products WHERE price > 6000.00;

重要提醒：不要用 real 或 double precision 存货币。比如 0.1 + 0.2 在浮点数中可能等于 0.30000000000000004，这在财务系统中是不可接受的。

字符串与文本类型：文本世界的分类

在 PostgreSQL 中，字符串类型不是“一个就够了”，而是根据用途细分。

varchar 与 text：灵活与自由的选择

• text：无长度限制，适合存储任意长度文本，如文章正文、日志内容。
• varchar(n)：最大长度为 n，适合长度固定的字段，如邮箱、用户名。

-- 创建一个文章表，使用 text 存储正文，varchar 存标题
CREATE TABLE articles (
    id serial PRIMARY KEY,
    title varchar(200) NOT NULL,           -- 标题不会太长
    content text NOT NULL,                 -- 正文可能很长
    author varchar(50) NOT NULL            -- 作者名长度固定
);

-- 插入一篇长文
INSERT INTO articles (title, content, author) VALUES (
    'PostgreSQL 数据类型入门指南',
    '本文详细介绍了 PostgreSQL 中的各类数据类型...（此处省略大量内容）',
    '张三'
);

小技巧：如果你不确定字段长度，优先用 text。PostgreSQL 对 text 的优化很好，性能几乎和 varchar 一致，且无需设置上限。

char 与 varchar 的区别：固定 vs 可变

• char(10)：固定长度，不足会用空格填充。例如存 "abc" 会变成 "abc "。
• varchar(10)：只存实际字符，不补空格。

-- 演示 char 的填充行为
CREATE TABLE test_char (
    name char(5),
    name_varchar varchar(5)
);

INSERT INTO test_char (name, name_varchar) VALUES ('张三', '张三');

-- 查询结果
SELECT 
    name, 
    length(name) as len_char,          -- 输出 5（含填充空格）
    name_varchar, 
    length(name_varchar) as len_varchar  -- 输出 2
FROM test_char;

建议：除非你有特殊需求（如数据库索引对固定长度更友好），否则一律用 varchar 或 text，避免空格带来的麻烦。

日期与时间类型：时间的精准表达

时间是业务的核心，错误的时间处理可能导致订单时间混乱、报表统计偏差。

PostgreSQL 提供了 5 种时间类型，每一种都对应不同的使用场景。

-- 创建订单表，记录下单时间
CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    created_at timestamptz DEFAULT now(),  -- 自动记录当前时间，带时区
    shipped_date date,                     -- 仅记录日期，如 2024-01-02
    delivery_time time                     -- 仅记录时间，如 14:30:00
);

-- 插入数据
INSERT INTO orders (shipped_date, delivery_time) VALUES 
    ('2024-01-02', '14:30:00');

-- 查询近 7 天的订单
SELECT * FROM orders 
WHERE created_at >= now() - interval '7 days';

关键点：使用 timestamptz 而不是 timestamp，可以避免时区问题。比如北京和纽约用户下单，系统会自动转换为统一时间标准（UTC）存储，再根据用户时区显示。

布尔与枚举类型：逻辑与状态的表达

在业务中，我们经常需要表示“是/否”、“已处理/未处理”这样的状态。

布尔类型：true 与 false 的世界

-- 创建一个任务表，用布尔类型表示是否完成
CREATE TABLE tasks (
    task_id serial PRIMARY KEY,
    title varchar(100) NOT NULL,
    is_completed boolean DEFAULT false,
    priority integer
);

-- 标记任务为已完成
UPDATE tasks SET is_completed = true WHERE task_id = 1;

-- 查询未完成的任务
SELECT * FROM tasks WHERE is_completed = false;

小贴士：布尔值只接受 true、false 或 null，不要用 1/0 或 'Y'/'N'，这会降低可读性。

枚举类型：自定义状态值

当状态不是简单的“是/否”，而是多个固定值时，枚举类型非常实用。

-- 创建一个订单状态枚举
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- 使用枚举类型创建表
CREATE TABLE order_status_log (
    id serial PRIMARY KEY,
    order_id integer,
    status order_status NOT NULL,
    updated_at timestamptz DEFAULT now()
);

-- 插入数据
INSERT INTO order_status_log (order_id, status) VALUES 
    (1001, 'processing'),
    (1002, 'shipped');

-- 查询某个状态的订单
SELECT * FROM order_status_log WHERE status = 'shipped';

优点：枚举类型确保数据一致性，数据库只会接受预定义的值，防止输入错误如 'shippedd'。

数组与 JSON 类型：现代应用的灵活数据结构

随着应用复杂度上升，单一字段无法满足需求。PostgreSQL 提供了强大的扩展类型。

创建数组与初始化

-- 创建一个用户标签表，使用数组存储多个标签
CREATE TABLE users (
    id serial PRIMARY KEY,
    username varchar(50) NOT NULL,
    tags text[]  -- 定义一个文本数组
);

-- 插入数据，数组使用 {值1, 值2} 格式
INSERT INTO users (username, tags) VALUES 
    ('alice', ARRAY['前端', 'Vue', 'JavaScript']),
    ('bob', ARRAY['后端', 'Java', 'Spring']);

-- 查询包含“JavaScript”的用户
SELECT username FROM users WHERE 'JavaScript' = ANY(tags);

ANY 是数组查询的关键操作符，表示“是否存在于数组中”。

JSON 类型：结构化数据的容器

-- 创建一个配置表，存储 JSON 格式数据
CREATE TABLE app_configs (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    config jsonb NOT NULL  -- 使用 jsonb，支持索引和高效查询
);

-- 插入 JSON 数据
INSERT INTO app_configs (name, config) VALUES 
    ('payment', '{"timeout": 30, "retry": 3, "method": "alipay"}');

-- 查询配置中的 timeout
SELECT config->>'timeout' AS timeout_value FROM app_configs WHERE name = 'payment';

-- 使用 jsonb 检查是否存在某个字段
SELECT * FROM app_configs 
WHERE config ? 'retry';  -- 检查是否有 retry 字段

建议：优先使用 jsonb 而不是 json，因为它支持索引、去重、高效查询，性能更优。

总结：合理选择，才能高效开发

PostgreSQL 数据类型不仅仅是“选什么”，更是“为什么选”。从整数到 JSON，从时间到数组，每一种类型都为特定场景而生。

记住：

• 财务数据用 numeric，别用浮点数；
• 时间用 timestamptz，避免时区陷阱；
• 长文本用 text，别设无意义长度；
• 状态用 enum，保证数据一致性；
• 复杂结构用 jsonb，灵活又高效。

当你开始设计表结构时，不妨多问一句：“这个字段最可能存什么类型？有没有可能被误用？” 这种思考习惯，是写出高性能、高可靠数据库系统的起点。

掌握 PostgreSQL 数据类型，是你通往专业数据库开发的必经之路。