数据库中的每一列都必须声明类型。类型告诉 PostgreSQL 这一列允许保存什么数据、怎样比较、怎样排序,以及可以执行哪些运算。
例如,任务标题适合使用文本,优先级适合使用整数,截止时间适合使用带时区的时间:
1CREATE TABLE task_type_example ( 2 title text, 3 priority smallint, 4 completed boolean, 5 due_at timestamptz 6);
Python 也有 str、int、bool 等类型,但 Python 类型和数据库类型并不是同一个系统。Python 对象需要经过数据库驱动转换,才能成为 PostgreSQL 能保存的值:
str
int
bool
text
varchar
smallint
integer
bigint
numeric
float
double precision
Decimal
boolean
True
False
date
tzinfo
datetime
timestamp
timestamptz
timedelta
interval
UUID
uuid
bytes
bytearray
memoryview
bytea
list
Json
Jsonb
json
jsonb
数字
PostgreSQL 的数字类型可以分为整数、精确小数和近似浮点数。它们在存储空间、取值范围和计算精度上各不相同:
numeric(p, s)
decimal(p, s)
p
s
real
不要因为 bigint 范围更大就无条件使用它,也不要为了节省几个字节把所有数字都设为 smallint。integer 在范围、空间和性能之间比较均衡。只有已经知道数据规模会超过它的上限时,才需要选择 bigint。
小数需要区分精确值和近似值:
1CREATE TABLE prices ( 2 amount numeric(12, 2), 3 confidence double precision 4);
numeric(12, 2) 的精度是 12,标度是 2,也就是总共最多 12 位数字,其中小数部分保留 2 位,整数部分最多 10 位。写入更多小数位时,PostgreSQL 会先舍入到 2 位;如果舍入后的整数部分超过 10 位,则会报错。decimal 与 numeric 在 PostgreSQL 中是等价类型。
numeric(12, 2)
decimal
real 和 double precision 是浮点数,适合统计、测量等允许微小误差的场景。它们的计算通常比 numeric 更快,但保存的是二进制近似值,直接使用等号比较计算结果也可能得到意外结果。
金额不要使用浮点数。0.1 + 0.2 一类计算可能受到二进制浮点表示影响,而 numeric 会按十进制精确保存和计算。
0.1 + 0.2
PostgreSQL 还提供 money 类型,但它的显示格式和小数精度会受到区域设置影响,跨环境迁移和计算时更容易产生困惑。业务金额通常使用 numeric(p, s) 更清晰。
money
布尔
boolean 可以保存 true、false,如果列允许为空,还可以保存 NULL。因此,可空布尔列实际表达了三种状态。只有业务确实存在“未知”状态时才应该允许它为空,否则应配合 NOT NULL 和合理的默认值使用。
true
false
NULL
NOT NULL
1CREATE TABLE boolean_example ( 2 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 3 is_completed boolean NOT NULL DEFAULT false, 4 is_archived boolean 5);
is_completed 是典型的二态字段:每一行都必须明确表示是否完成,省略该列时默认为 false。is_archived 没有设置 NOT NULL,因此还可以用 NULL 表示“尚未确定是否归档”。是否允许这种第三种状态,应由业务含义决定。
is_completed
is_archived
此段落已有评论,按 Enter 查看评论及回复列表。
连接 GitHub 后查看和发表评论,也可在 GitHub 查看公开讨论。
1. 类型
数据库中的每一列都必须声明类型。类型告诉 PostgreSQL 这一列允许保存什么数据、怎样比较、怎样排序,以及可以执行哪些运算。
例如,任务标题适合使用文本,优先级适合使用整数,截止时间适合使用带时区的时间:
Python 也有
str、int、bool等类型,但 Python 类型和数据库类型并不是同一个系统。Python 对象需要经过数据库驱动转换,才能成为 PostgreSQL 能保存的值:strtext、varcharintsmallint、integer、bigint、numericfloatdouble precisionDecimalnumericboolbooleanTrue和Falsedatedatetzinfo的datetimetimestamptzinfo的datetimetimestamptztimedeltaintervalUUIDuuidbytes、bytearray、memoryviewbytealistJson、Jsonb包装的对象json、jsonb2. 基础类型
数字
PostgreSQL 的数字类型可以分为整数、精确小数和近似浮点数。它们在存储空间、取值范围和计算精度上各不相同:
smallintintegerbigintinteger范围的编号或计数,例如大型系统的主键numeric(p, s)/decimal(p, s)p为总位数,s为小数位数realdouble precision不要因为
bigint范围更大就无条件使用它,也不要为了节省几个字节把所有数字都设为smallint。integer在范围、空间和性能之间比较均衡。只有已经知道数据规模会超过它的上限时,才需要选择bigint。小数需要区分精确值和近似值:
numeric(12, 2)的精度是 12,标度是 2,也就是总共最多 12 位数字,其中小数部分保留 2 位,整数部分最多 10 位。写入更多小数位时,PostgreSQL 会先舍入到 2 位;如果舍入后的整数部分超过 10 位,则会报错。decimal与numeric在 PostgreSQL 中是等价类型。real和double precision是浮点数,适合统计、测量等允许微小误差的场景。它们的计算通常比numeric更快,但保存的是二进制近似值,直接使用等号比较计算结果也可能得到意外结果。金额不要使用浮点数。
0.1 + 0.2一类计算可能受到二进制浮点表示影响,而numeric会按十进制精确保存和计算。PostgreSQL 还提供
money类型,但它的显示格式和小数精度会受到区域设置影响,跨环境迁移和计算时更容易产生困惑。业务金额通常使用numeric(p, s)更清晰。布尔
boolean可以保存true、false,如果列允许为空,还可以保存NULL。因此,可空布尔列实际表达了三种状态。只有业务确实存在“未知”状态时才应该允许它为空,否则应配合NOT NULL和合理的默认值使用。is_completed是典型的二态字段:每一行都必须明确表示是否完成,省略该列时默认为false。is_archived没有设置NOT NULL,因此还可以用NULL表示“尚未确定是否归档”。是否允许这种第三种状态,应由业务含义决定。