PostgreSQL 的每一列都有固定的类型,在 CREATE TABLE 中指定。大多数表只需要几种:整数用 bigint 或 integer,金额用 numeric,字符串用 text,还有 boolean,时间点用 timestamptz,文档用 jsonb。pg_typeof() 可以告诉你任意值的类型。
主要类型及适用场景
| 类型 | 存放内容 | 适合用于 |
|---|---|---|
integer (int) | 最大约 21 亿的整数,4 字节 | 计数、数量、较小的 id |
bigint | 最大约 922 亿亿的整数,8 字节 | 主键,以及任何会不断增长的值 |
smallint | 最大 32767,2 字节 | 很少值得用 |
numeric(p, s) | 精确小数 | 金额、比率、任何要求和的值 |
real, double precision | 二进制浮点数 | 测量值、科学计算数值 |
text | 任意长度的字符串 | 名字、邮箱、描述 |
varchar(n) | 有长度限制的字符串 | 长度限制是规则时 |
boolean | true、false 或 NULL | 标志位 |
date | 日历上的某一天 | 生日、截止日期 |
timestamptz | 一个时间点 | 创建时间和更新时间 |
interval | 一段时长 | '2 hours'、'30 days' |
uuid | 128 位标识符 | 公开的或分布式的 id |
jsonb | 解析后的 JSON | 没有固定结构的数据 |
text[], int[] | 任意类型的数组 | 短列表、标签 |
bytea | 原始字节 | 较小的二进制值 |
更重要的几个选择有单独的页面:timestamp 和 timestamptz、枚举类型、jsonb 以及数组。id(serial、标识列、uuid)在主键页面中介绍。
numeric 和 double precision
numeric 精确地存储小数。real 和 double precision 存储的是二进制小数,所以像 0.1 这样的值只是近似值,求和时会出现偏差:
numeric 的和是 0.3。浮点数的和是 0.30000000000000004,相等判断的结果是 false。金额请用 numeric:numeric(10, 2) 总共允许 10 位数字,小数点后 2 位,插入时多出的小数位会被四舍五入。
整数除以整数会丢掉小数部分:
7 / 2 返回 3。把其中一边变成 numeric(带小数点的字面量,或者做一次类型转换),就能得到 3.5。
text、varchar(n) 和 char(n)
三者的存储方式相同,没有哪个更快。varchar(n) 在插入时会拒绝更长的值,只有当长度限制是数据本身的规则时才有用。char(n) 会用空格补齐,在新表中没有用处。显式转换成 varchar(n) 会截断,而不是报错。
这个类型转换返回 Post。char(5) 的值占 5 个字节,但 length 忽略补齐的空格,返回 2。
布尔值和 NULL
boolean 列接受 true、false、't'、'yes'、'on'、'1' 以及它们的反义值,返回时总是 true 或 false。缺失的值是 NULL,它既不是真也不是假,所以 WHERE NOT active 会跳过 active 为 NULL 的行。
如果 NULL 应该算作假,就用 IS TRUE、IS NOT TRUE 或 COALESCE(active, false)。
常见问题
PostgreSQL 中 text 和 varchar 有性能差别吗?
没有。text、varchar 和 varchar(n) 的存储方式完全相同。varchar(n) 只是多了一个长度检查,所以除非长度限制是数据本身的规则,否则用 text 就行。
PostgreSQL 里应该用 money 类型吗?
通常不应该。它的输入和输出取决于 lc_monetary 设置,而且值本身不记录币种。把金额存在 numeric(12,2) 里,币种单独放一列。
PostgreSQL 中和 DATETIME 对应的是什么类型?
表示时间点用 timestamptz(timestamp with time zone),表示不带时区的挂钟时间用 timestamp。PostgreSQL 没有 DATETIME 类型。
在 PostgreSQL 中怎么查看一个值或一列的类型?
SELECT pg_typeof(expression) 返回任意表达式的类型,列也可以。在 psql 中,\d table_name 会列出每一列及其类型。