PostgreSQL 教程

Appearance

8.1. 数字类型# ​

8.1.1. 整数类型
8.1.2. 任意精度数字
8.1.3. 浮点数类型
8.1.4. 序列类型

数字类型包括两字节、四字节和八字节整数，四字节和八字节浮点数，以及可选精度的十进制数。表 8.2列出了可用的类型。

表 8.2. 数字类型

名称存储大小说明范围
smallint2 字节小范围整数-32768 至 +32767
integer4 字节整数的典型选择-2147483648 至 +2147483647
bigint8 字节大范围整数-9223372036854775808 至 +9223372036854775807
decimal可变用户指定的精度，精确小数点前最多 131072 位数字；小数点后最多 16383 位数字
numeric可变用户指定的精度，精确小数点前最多 131072 位数字；小数点后最多 16383 位数字
实数4 字节可变精度，不精确6 位小数精度
double precision8 字节可变精度，不精确15 位小数精度
smallserial2 字节小自增整数1 至 32767
serial4 字节自增整数1 至 2147483647
bigserial8 字节大自增整数1 至 9223372036854775807

数值类型的常量语法在第 4.1.2 节中进行了描述。数值类型具有全套对应的算术运算符和函数。有关更多信息，请参阅第 9 章。以下各节详细描述了这些类型。

8.1.1. 整数类型# ​

类型smallint、integer和bigint存储整数，即没有分数部分的数字，范围各不相同。尝试存储超出允许范围的值将导致错误。

类型integer是常见选择，因为它在范围、存储大小和性能之间提供了最佳平衡。smallint类型通常仅在磁盘空间非常宝贵时使用。bigint类型设计为在integer类型的范围不足时使用。

SQL仅指定整数类型integer（或int）、smallint和bigint。类型名称int2、int4和int8是扩展，也由其他一些SQL数据库系统使用。

8.1.2. 任意精度数字# ​

类型numeric可以存储具有非常多位数字的数字。特别建议用于存储货币金额和其他需要精确性的数量。使用numeric值进行的计算在可能的情况下产生精确的结果，例如加法、减法、乘法。但是，与整数类型或下一节中描述的浮点类型相比，对numeric值进行的计算非常慢。

我们在下面使用以下术语：numeric的精度是整数中有效数字的总数，即小数点两侧的数字数。numeric的刻度是小数部分中十进制数字的计数，即小数点右侧的计数。因此，数字 23.5141 的精度为 6，刻度为 4。整数可以被认为具有零刻度。

numeric列的最大精度和最大刻度都可以配置。要声明类型为numeric的列，请使用语法

NUMERIC(precision, scale)

精度必须为正数，而刻度可以为正数或负数（见下文）。或者

NUMERIC(precision)

选择刻度 0。指定

NUMERIC

如果没有指定任何精度或刻度，则会创建一个“无约束 numeric”列，其中可以存储任何长度的数字值，直至实现限制。这种类型的列不会将输入值强制转换为任何特定刻度，而具有声明刻度的numeric列会将输入值强制转换为该刻度。（SQL标准要求默认刻度为 0，即强制转换为整数精度。我们发现这有点没用。如果您担心可移植性，请始终明确指定精度和刻度。）

注意

在numeric类型声明中可以明确指定的最大精度为 1000。无约束numeric列受表 8.2中描述的限制。

如果要存储的值的标度大于列的声明标度，系统会将该值舍入到指定的小数位数。然后，如果小数点左侧的位数超过声明精度减去声明标度，则会引发错误。例如，声明为

NUMERIC(3, 1)

的列会将值舍入到 1 位小数，并且可以存储 -99.9 到 99.9（包括 -99.9 和 99.9）之间的值。

从PostgreSQL15 开始，允许声明标度为负数的numeric列。然后，值会舍入到小数点左侧。精度仍然表示未舍入的数字的最大数量。因此，声明为

NUMERIC(2, -3)

的列会将值舍入到最接近的千位，并且可以存储 -99000 到 99000（包括 -99000 和 99000）之间的值。还允许声明大于声明精度的标度。此类列只能保存小数值，并且要求小数点右侧的零位数至少为声明标度减去声明精度。例如，声明为

NUMERIC(3, 5)

的列会将值舍入到 5 位小数，并且可以存储 -0.00999 到 0.00999（包括 -0.00999 和 0.00999）之间的值。

注意

PostgreSQL允许numeric类型声明中的标度为 -1000 到 1000 范围内的任何值。但是，SQL标准要求标度在 0 到*precision*范围内。使用该范围之外的标度可能无法移植到其他数据库系统。

数值以物理方式存储，没有任何额外的前导或尾随零。因此，列的声明精度和标度是最大值，而不是固定分配。（从这个意义上说，numeric类型更类似于varchar(*n*)而不是char(*n*)。）实际存储要求是每组四个十进制数字两个字节，加上三到八个字节的开销。

除了普通数值外，numeric类型还有几个特殊值

无穷大

- 无穷大

NaN

这些值改编自 IEEE 754 标准，分别表示““无穷大””、““负无穷大””和““非数字””。在 SQL 命令中将这些值写为常量时，必须用引号将它们引起来，例如UPDATE table SET x = '-Infinity'。在输入时，这些字符串以不区分大小写的方式识别。无穷大值也可以拼写为inf和-inf。

无穷大值的行为符合数学预期。例如，Infinity加上任何有限值等于Infinity，Infinity加上Infinity也等于Infinity；但Infinity减去Infinity等于NaN（非数字），因为它没有明确的解释。请注意，无穷大只能存储在不受约束的numeric列中，因为它在概念上超过了任何有限精度限制。

NaN（非数字）值用于表示未定义的计算结果。通常，任何带有NaN输入的操作都会产生另一个NaN。唯一的例外是当操作的其他输入使得如果用任何有限或无限数字值替换NaN，将获得相同的输出；然后，该输出值也用于NaN。（此原理的一个示例是NaN提升到零次幂会产生一。）

注意

在大多数“非数字”概念的实现中，NaN不被视为等于任何其他数字值（包括NaN）。为了允许对numeric值进行排序并在基于树的索引中使用它们，PostgreSQL将NaN值视为相等，并且大于所有非NaN值。

类型decimal和numeric是等效的。这两种类型都是SQL标准的一部分。

在舍入值时，numeric类型将舍入值舍入到远离零，而（在大多数计算机上）real和double precision类型将舍入值舍入到最接近的偶数。例如

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4
(8 rows)

8.1.3. 浮点类型# ​

数据类型real和double precision是不精确的、可变精度的数字类型。在当前支持的所有平台上，这些类型都是IEEE标准 754 二进制浮点算术（分别为单精度和双精度）的实现，前提是底层处理器、操作系统和编译器支持它。

不精确意味着某些值无法精确转换为内部格式，并且存储为近似值，因此存储和检索值可能会出现细微差异。管理这些错误以及它们如何在计算中传播是整个数学和计算机科学分支的主题，这里将不讨论，除了以下几点

  • 如果您需要精确的存储和计算（例如货币金额），请改用 numeric 类型。

  • 如果您想对这些类型进行复杂的计算，尤其是当您依赖边界情况（无穷大、下溢）中的特定行为时，您应该仔细评估实现。

  • 比较两个浮点值是否相等可能无法始终按预期工作。

在所有当前受支持的平台上，real类型的范围约为 1E-37 至 1E+37，精度至少为 6 位小数。double precision类型的范围约为 1E-307 至 1E+308，精度至少为 15 位。太大或太小的值将导致错误。如果输入数字的精度太高，可能会进行舍入。太接近于零且无法表示为与零不同的数字将导致下溢错误。

默认情况下，浮点值以其最短精确十进制表示形式以文本形式输出；产生的十进制值更接近于真实的存储二进制值，而不是以相同二进制精度表示的任何其他值。（但是，输出值当前从不完全介于两个可表示值之间，以避免输入例程不正确地遵守舍入到最接近偶数规则的广泛错误。）对于float8值，此值最多使用 17 位有效小数位，对于float4值，最多使用 9 位有效小数位。

注意

这种最短精确输出格式比历史舍入格式生成得快得多。

为了与PostgreSQL旧版本生成的输出兼容，并允许降低输出精度，可以使用extra_float_digits参数来选择舍入的十进制输出。设置值为 0 会恢复将值舍入为 6（对于float4）或 15（对于float8）个有效小数位的先前默认值。设置负值会进一步减少数字数量；例如，-2 将分别将输出舍入为 4 或 13 位。

任何大于 0 的extra_float_digits值都会选择最短精确格式。

注意

历史上，想要精确值的应用程序必须将extra_float_digits设置为 3 才能获得它们。为了在版本之间实现最大的兼容性，他们应该继续这样做。

除了普通数字值之外，浮点类型还有几个特殊值

无穷大

- 无穷大

NaN

这些分别表示 IEEE 754 特殊值“无穷大”、“负无穷大”和“非数字”。在 SQL 命令中将这些值写为常量时，必须用引号将它们引起来，例如UPDATE table SET x = '-Infinity'。在输入时，这些字符串以不区分大小写的方式被识别。无穷大值还可以拼写为inf和-inf。

注意

IEEE 754 规定NaN不应与任何其他浮点值（包括NaN）相等。为了允许对浮点值进行排序并在基于树的索引中使用它们，PostgreSQL将NaN值视为相等，并且大于所有非NaN值。

PostgreSQL还支持 SQL 标准符号float和float(*p*)来指定不精确的数字类型。在此，p指定以二进制数字表示的最小可接受精度。PostgreSQL接受float(1)到float(24)来选择real类型，而float(25)到float(53)选择double precision。超出允许范围的*p*值会引发错误。float未指定精度时，表示double precision。

8.1.4. 序列类型# ​

注意

本节介绍一种 PostgreSQL 特有的创建自动增量列的方法。另一种方法是使用 SQL 标准标识列功能，详见CREATE TABLE。

数据类型smallserial、serial和bigserial不是真正的类型，而只是创建唯一标识符列的一种符号便利（类似于某些其他数据库支持的AUTO_INCREMENT属性）。在当前实现中，指定

CREATE TABLE tablename (
    colname SERIAL
);

等效于指定

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

因此，我们创建了一个整数列，并安排从序列生成器分配其默认值。应用NOT NULL约束以确保无法插入空值。（在大多数情况下，您还需要附加UNIQUE或PRIMARY KEY约束，以防止意外插入重复值，但这并不是自动的。）最后，该序列被标记为“由”该列“拥有”，以便在删除该列或表时将其删除。

注意

由于smallserial、serial和bigserial是使用序列实现的，因此即使从未删除任何行，列中出现的数值序列中也可能存在“空洞”或间隙。即使包含该值的行的从未成功插入到表列中，从序列分配的值仍然会被“用完”。例如，如果插入事务回滚，可能会发生这种情况。有关详细信息，请参见第 9.17 节中的nextval()。

要将序列的下一个值插入到serial列中，请指定serial列应分配其默认值。这可以通过从INSERT语句中的列列表中排除该列，或通过使用DEFAULT关键字来完成。

类型名称serial和serial4是等效的：两者都创建integer列。类型名称bigserial和serial8的工作方式相同，只是它们创建bigint列。如果预计在表的生命周期内使用超过 231个标识符，则应使用bigserial。类型名称smallserial和serial2的工作方式也相同，只是它们创建smallint列。

为serial列创建的序列在拥有该列被删除时会自动删除。您可以在不删除列的情况下删除序列，但这会强制删除列默认表达式。