Home

深色模式

PostgreSQL 常用数据类型 ​

概述 ​

PostgreSQL 的类型很多，但项目里高频出现的，其实就几类：整数、文本、数值、时间、布尔，再加上 UUID、JSONB 和数组。类型选得顺手，后面的约束、索引、SQL 和迁移都更好写；类型选得别扭，数据库和应用代码会一起拧巴。

这一篇不追求把官方整张类型表背下来，只讲日常建模最常做选择的部分。目标不是“知道 PostgreSQL 支持多少类型”，而是“知道一张业务表应该先用哪些类型”。

主键类型 ​

最常见的主键方案还是两类：

  • 整数主键
  • UUID 主键

如果系统主要跑在单库里，主键只在数据库内部或后端服务之间流转，整数主键通常最直接：

sql
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

BIGINT 配合 IDENTITY，已经能覆盖绝大多数业务表。它简单、好读、索引也更紧凑。

如果主键要暴露给外部系统、前端接口，或者未来存在多库、多节点写入需求，UUID 会更合适：

sql
id UUID PRIMARY KEY

别因为 UUID 看起来更“高级”就默认全表都上。它不是错，只是有成本：索引更大，可读性更差，排障时也不如整数直观。

整型选择 ​

除了主键，常见整数类型还有：

  • SMALLINT：范围很小的状态值、短编号
  • INTEGER：大多数普通计数场景
  • BIGINT：订单量、日志量、累计值这类可能长得更大的场景

如果一开始就知道值可能会很大，直接用 BIGINT 没问题。比起后面改列类型，起步时多给一点空间通常更省心。

文本类型 ​

PostgreSQL 对 TEXT 和不带长度上限的 VARCHAR 处理非常接近，所以业务字段很多时候直接用 TEXT 就够了：

sql
nickname TEXT NOT NULL,
email TEXT NOT NULL

什么时候才值得写 VARCHAR(50)、VARCHAR(255)？通常只有两种情况：

  • 长度限制本身就是明确业务规则
  • 需要把这个限制直接放进数据库层

如果只是“别的数据库里大家都这么写”，那大概率没有额外收益。

数值类型 ​

涉及金额、手续费、汇率、积分折算这类不能接受精度误差的场景，优先用 NUMERIC：

sql
amount NUMERIC(12, 2) NOT NULL

REAL、DOUBLE PRECISION 更适合允许误差的数值计算，不适合财务类数据。这个坑不新鲜，但还是经常有人踩。

如果金额永远按最小货币单位保存，例如分、厘，也可以直接用整数列。这是另一条很常见的路线。

时间类型 ​

大多数业务记录时间，优先用：

  • DATE：只关心日期
  • TIMESTAMPTZ：关心具体时间点

例如：

sql
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

TIMESTAMPTZ 存的是明确的时间点，展示时再按会话时区转换。只要应用和数据库的时区策略一致，后面查日志、对时间线都会轻松很多。

相对来说，TIMESTAMP 更适合不带时区语义的本地时间，比如“门店营业时间模板”“某天 09:00 到 18:00 的班次规则”。普通业务记录时间，优先选 TIMESTAMPTZ 更稳。

布尔类型 ​

是非值就用 BOOLEAN：

sql
is_active BOOLEAN NOT NULL DEFAULT TRUE

不要把明确的真/假状态写成 SMALLINT、INTEGER 或 'Y'/'N'。字段含义应该让人一眼看懂，不该靠约定猜。

状态字段 ​

状态值不多、变化不频繁时，直接用文本配合检查约束通常很实用：

sql
status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))

这样做的好处是：

  • 读 SQL 很直观
  • 迁移时比自定义枚举灵活
  • 约束依然在数据库层生效

项目早期，这通常比急着上自定义 ENUM 更省事。

JSONB ​

JSONB 适合这些场景：

  • 第三方回调原文要保留
  • 扩展字段暂时不稳定
  • 某些配置项结构会演进

例如：

sql
metadata JSONB NOT NULL DEFAULT '{}'::jsonb

配合查询也不难：

sql
SELECT *
FROM orders
WHERE metadata->>'channel' = 'mobile';

但它不是“懒得建表”时的万能出口。核心业务字段如果已经稳定，例如用户 ID、订单金额、支付状态，就应该老老实实拆成普通列。否则后面做约束、索引和统计都会更难。

数组字段 ​

数组在 PostgreSQL 里很好用：

sql
tags TEXT[] NOT NULL DEFAULT '{}'

它适合轻量、多值、顺序不重要的数据，比如文章标签、允许的来源列表、某些开关集合。

但如果元素本身有独立生命周期，或者需要复杂筛选、关联和去重，最好还是拆成独立表。比如“用户拥有多个角色”通常更适合关联表，而不是直接在数组里塞一串角色名。

默认表结构 ​

下面这张表，已经覆盖了很多日常业务模型会用到的类型：

sql
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    nickname TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    profile JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

如果业务确实需要外部可见主键、精确金额和状态值，再把 UUID、NUMERIC、CHECK 这些组合进去就够了。多数项目的问题不在于类型太少，而是类型选得太花。

常见误区 ​

到处写 VARCHAR(255) ​

如果只是习惯动作，意义通常不大。长度限制应该有明确业务理由，而不是为了看起来更正式。

时间字段一律用 TIMESTAMP ​

多时区、容器部署、日志对齐时，这种设计很容易把人绕晕。普通业务记录时间，优先用 TIMESTAMPTZ。

把稳定字段全塞进 JSONB ​

JSONB 很方便，但核心结构字段还是应该拆出来。约束、索引、聚合和维护都更清楚。

参考 ​