PostgreSQL 数据类型:选对类型省一半心

接手过一个老项目,users 表里 phone 是 VARCHAR(255)、age 是 INTEGER、balance 是 DOUBLE PRECISION、created_at 是 TIMESTAMP 不带时区、status 是 VARCHAR(20) 存着 'active' / 'inactive' 字符串。能跑吗?能跑。对吗?几乎全错。

数据类型不是"能存进去就行"。类型选错,代价是连锁的:精度在不知不觉中丢失、索引比该有的大一截还更慢、存储白白浪费三倍、查询时还得 CAST 来 CAST 去。更糟的是,类型是 schema 的地基,一旦线上跑起来,改类型的迁移又贵又险。选对类型,本身就是 schema 设计的半壁江山。

这篇把 PostgreSQL 最常用的几类类型讲清楚,重点不是"有哪些",而是怎么选、为什么。

一、数字:整数按范围,钱必须 numeric

数字类型分两条线:整数和带小数的。

整数有 smallint(2 字节,-32768~32767)、integer(4 字节,21 亿级)、bigint(8 字节,922 亿亿级)。原则很简单——按范围选,别动不动 bigint。状态码、年龄、排序字段用 integer 绰绰有余,全用 bigint 每行多 4 字节,索引、外键、WAL 全跟着膨胀,一张大表下来几个 GB 就这么没了。smallint 适合枚举性质的数值(优先级 1-5、星期几),省一半空间。

自增主键用 serial / bigserial(PG 10+ 推荐用 GENERATED ALWAYS AS IDENTITY,语法更标准、行为更干净,serial 是历史包袱)。中小表 integer 够,但只要这张表可能涨到 21 亿行(日志、事件、埋点),直接 bigint,别留隐患——integer 溢出那天,迁移是地狱。

带小数的才是重灾区。real(4 字节)/ double precision(8 字节)是浮点,二进制无法精确表示十进制小数,0.1 + 0.2 不等于 0.3。存科学计算、传感器读数没问题,但存钱绝对不行:

-- 坏:用浮点存钱,迟早出事
ALTER TABLE accounts ADD COLUMN balance DOUBLE PRECISION;
-- 0.1 + 0.2 = 0.30000000000000004,对账时你会哭

-- 好:用 numeric 存钱,精度无损
ALTER TABLE accounts ADD COLUMN balance NUMERIC(12, 2);
-- 12 位总长,2 位小数,最大 9999999999.99,绝对精确

numeric(p, s) / decimal(p, s) 是精确十进制,p 是总位数,s 是小数位。代价是计算比浮点慢、占空间是变长的,但对于钱、利率、税率这种,正确性 >> 性能,没得选。汇率计算那种既要求精度又要求范围大的,用 numeric 不指定精度(变精度 numeric),允许上千位有效数字。

一句话:整数按范围选,小数看是不是钱——钱用 numeric,科学数据用 double,泾渭分明。

二、文本:别再 VARCHAR(255) 万能了

文本类型里最常见的迷思是"VARCHAR(255) 万能"。这玩意儿是 MySQL 时代的遗毒(老版本 InnoDB 索引前缀限制 255 字节)。在 PostgreSQL 里,VARCHAR(n)、TEXT、VARCHAR(无长度)的存储方式完全一样,都是变长,区别只在有没有长度约束。

-- 坏:无脑 VARCHAR(255)
name        VARCHAR(255)   -- 邮箱、用户名、文章标题全 255,毫无依据
bio         VARCHAR(255)   -- 个人简介 255 字符根本不够

-- 好:按业务定,或干脆用 TEXT
email       VARCHAR(320)   -- RFC 规定邮箱最长 320
username    VARCHAR(32)    -- 业务规则就是 32 上限
bio         TEXT           -- 不限长,应用层约束

选型原则:VARCHAR(n) 用在"业务上真的有上限"的字段(邮箱、用户名、ISO 国家码这种固定长度的);拿不准或可能变长的(简介、备注、正文)直接 TEXT,长度约束放应用层。CHAR(n) 是定长、不足补空格,几乎用不上,PG 内部存储也还是变长,只在兼容老系统或固定编码字段(如 CHAR(2) 国家码)时考虑。

唯一标识别用 VARCHAR 存 UUID 字符串:

-- 坏:UUID 存成字符串
id  VARCHAR(36) PRIMARY KEY
-- 36 字节,字符串比较慢,索引大

-- 好:用原生 uuid 类型
id  UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- 16 字节,二进制存储,索引更小更快

uuid 类型只要 16 字节,是字符串的不到一半,比较是字节级、不用 collation,索引和 JOIN 都更快。PG 13+ 内置 gen_random_uuid(),直接当默认值用。

三、时间:请无脑 timestamptz

这是全文最重要的一节。时间字段,默认 TIMESTAMPTZ(带时区),别用 TIMESTAMP(不带时区)。

-- 坏:不带时区,埋雷
created_at  TIMESTAMP

-- 好:带时区
created_at  TIMESTAMPTZ DEFAULT now()

很多人以为"我又不做跨国业务,不需要时区"。错。不带时区的 TIMESTAMP 是个"裸时间",它的含义是模糊的——同一个 2026-07-17 10:00:00,服务部署在 UTC 还是东八区,解读完全不同。一旦你的服务器时区变了、容器默认 UTC、或者哪天业务出海,所有历史时间全部错位,且无法补救(你已经丢失了"这个时间是哪个时区的"这个信息)。

timestamptz 的真相是:存储永远是 UTC,只是按客户端会话时区显示。它不会多占空间(和 timestamp 一样 8 字节),却保留了一个关键信息——"这个时刻在世界范围内是唯一的"。写入时传 2026-07-17 10:00:00+08,存的是对应 UTC;读出来在 UTC 会话就是 02:00:00+00,在东八区会话就是 10:00:00+08。计算、比较、跨时区展示全都不会错。

配套类型:DATE(只有日期无时间)、TIME(只有时间无日期,少用)、INTERVAL(时间跨度,如 INTERVAL '30 days',做过期、轮询周期非常好用)。now() / CURRENT_TIMESTAMP 取当前时刻,AT TIME ZONE 'Asia/Shanghai' 做时区转换。

别用 Unix 时间戳整数存时间。能算,但丢了类型安全、丢了可读性、丢了 PG 丰富的时间函数,纯属给自己找麻烦。

四、布尔:就 boolean,别用 0/1

-- 坏
is_active  SMALLINT DEFAULT 1   -- 0/1,还要在应用层翻译

-- 好
is_active  BOOLEAN DEFAULT TRUE

boolean 占 1 字节,语义清晰,PG 的查询优化器、驱动、ORM 全部原生识别。用 SMALLINT 存 0/1 是 MySQL 老习惯(PG 之外也常见),纯属给自己和应用层增加心智负担——每次查询都要记得"1 是真",还容易写出 WHERE is_active 这种在 int 上行为不直观的 SQL。

五、枚举:enum、字符串、查表,怎么选

状态字段(status = pending/paid/cancelled)有三种存法,各有适用场景。

字符串(坏):status VARCHAR(20),值随便填。没有任何约束,迟早有人写入 'Paid'、'PAID'、'padi',查询时 WHERE status = 'paid' 漏掉一半。直接淘汰。

enum 类型(中等场景,最常用):

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status  order_status NOT NULL DEFAULT 'pending'
);

enum 在 PG 里是真正的类型,内部 4 字节,类型安全(写错值直接报错)、可读性好、省空间。适合值集稳定的场景:订单状态、用户角色、优先级。坑是改值麻烦:加值要 ALTER TYPE ... ADD VALUE(PG 12+ 支持事务内加,但有限制),删值、改名更要走迁移、甚至重建表。所以 enum 适合"几乎不变"的枚举;业务上还在演进的,慎用。

查表(大场景):

CREATE TABLE order_statuses (code TEXT PRIMARY KEY, label TEXT, ...);
CREATE TABLE orders (..., status TEXT REFERENCES order_statuses(code));

值集大、需要额外属性(每个状态有中文名、描述、排序)、或频繁变更,用独立的字典表 + 外键。灵活但开销更大(一次 JOIN)。

实战经验:核心、稳定的枚举用 enum;值集会涨、或带元数据的用查表;字符串方案只在原型期临时用。

六、数组与 JSONB:PG 的杀手锏

这两种是 PostgreSQL 区别于 MySQL 的招牌能力。

数组 int[] / text[] 适合简单的多值场景,比如文章标签、角色列表:

CREATE TABLE articles (
    id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tags   TEXT[] NOT NULL DEFAULT '{}'
);
-- 写入 tags ARRAY['postgres', 'db', 'backend']
-- 查询 tags && ARRAY['postgres']  (重叠判断)
-- 支持 GIN 索引:CREATE INDEX ON articles USING GIN (tags);

数组的好处是轻量、原子——不用为标签单独建表、不用 JOIN,GIN 索引让"包含某标签"的查询很快。但别滥用:一旦这些值需要独立属性、需要被多方引用、需要统计聚合,还是老老实实建关联表。数组是"标签类简单多值"的甜点区。

JSONB 是半结构化数据的正解:字段结构不固定(用户自定义属性、第三方 API 原样存)、或同一张表要承载多种实体。JSONB 是二进制 JSON,支持 GIN 索引、支持丰富的查询操作符(->> 取文本、@> 包含判断、? 键存在判断)。注意区分 JSON 和 JSONB:前者保留原始文本(含空格、顺序),后者解析存储、查询快、能索引,99% 场景用 JSONB。

JSONB 的坑和最佳实践(什么时候该用、什么时候该抽成关系字段、怎么建索引、怎么处理 schema 演进)是另一个大话题,这篇不展开,单独文章细聊。这里只给一个原则:结构稳定的字段坚决抽成原生列(能建索引、能约束、查询快),JSONB 留给真正动态、 schema-less 的部分。把整个业务塞进一个大 JSONB,你很快会怀念关系模型。

小结

• 类型选错是连锁代价:精度丢失、索引膨胀、存储浪费、查询要 CAST、迁移又贵又险。类型是 schema 地基,选对是设计的基本功。
• 数字按用途分:整数按范围选(integer 优先,溢出风险大的直接 bigint);钱、利率、税率一律 numeric,科学/传感器数据才用 double。绝不用浮点存钱。
• 文本别再 VARCHAR(255) 万能:有业务上限用 VARCHAR(n),可能变长直接 TEXT;UUID 用原生 uuid 类型(16 字节)而非字符串(36 字节)。
• 时间无脑 timestamptz:不带时区是定时炸弹,服务时区一变、业务一出海就全错。now() + timestamptz 是默认组合。
• 能用原生类型别用字符串万能方案:布尔用 boolean、固定枚举用 enum(值集稳定才用)、简单多值用数组、半结构化用 JSONB(动态部分才用,别整表塞)。

一句话收尾:PostgreSQL 给了你精确到"这个值到底是什么"的类型系统,别浪费它,把所有字段都退化成 VARCHAR。选对类型 = 性能 + 正确性 + 可维护性,一行 CREATE TABLE 里的取舍,决定了这张表未来三五年的命运。