Menu

PostgreSQL 数据类型:每种类型该怎么选

几乎每一列都会用到的 PostgreSQL 数据类型:整数、numeric、text、boolean、timestamptz、uuid、jsonb、数组和枚举,以及各自适合的场景。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

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)有长度限制的字符串长度限制是规则时
booleantrue、false 或 NULL标志位
date日历上的某一天生日、截止日期
timestamptz一个时间点创建时间和更新时间
interval一段时长'2 hours'、'30 days'
uuid128 位标识符公开的或分布式的 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 会列出每一列及其类型。

Coddy 编程语言插图

和 Coddy 一起学编程

开始