Postgresql 中文操作指南

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

类型 integer 是常用的选择，因为它在范围、存储大小和性能之间提供了最佳平衡。类型 smallint 通常仅在磁盘空间处于高级别时才使用。类型 bigint 在类型 integer 的范围不足时使用。

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

类型 numeric 可以存储具有大量数字的数字。特别推荐用于存储精确度要求的货币金额和其他数量。numeric 值的计算会产生尽可能精确的结果，例如加法、减法、乘法。但是，与整数类型或下一节描述的浮点类型相比，对 numeric 值的计算非常慢。

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

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

精度必须为正数，而小数位数可以为正数或负数（请参阅下文）。或者：

选择小数位数 0。指定：

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

在_numeric_类型声明中可以明确指定的最大精度为 1000。不受限制的_numeric_列受 Table 8.2中描述的限制。

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

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

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

会将值舍入到最接近的千位，并且可以存储 -99000 到 99000（含）之间的值。还可以声明大于声明精度的精度。此类列只能容纳小数值，并且它要求小数点右侧的零的个数至少为声明的小数 minus 声明的精度。例如，声明的列为

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

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

数值按原样存储，没有额外的前导或尾随零。因此，列的声明精度和小数为最大值，而不是固定分配。（从这个意义上说，numeric 类型更类似于 _varchar(_n）而不是 _char(_n）。实际存储需求是每组四位小数两个字节，外加三到八个字节的开销。

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

Infinity-Infinity_NaN_

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

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

NaN (不是数字) 值用于表示未定义的运算结果。一般来说，任何带有 NaN 输入的操作会产生另一个 NaN。唯一的例外是当运算的另一个输入是当 @{4} 用任何有穷或无限数值替换时，会得到相同输出；然后，那个输出值也用于 NaN。（此原则的一个例子是 @{6} 的零次方为 1。）

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

@{7} 和 @{8} 类型是等效的。两种类型都是 SQL 标准的一部分。

在舍入值时，@{9} 类型将舍弃值舍入为远离零的位置，而 @{10} 和 @{11} 类型（在大多数机器上）将舍弃值舍入为最接近的偶数值。例如：

@{12} 和 @{13} 数据类型是不精确、可变精度的数值类型。在所有目前受支持的平台上，这些类型都是 IEEE 标准 754 二进制浮点算术（分别为单精度和双精度）的实现，在底层处理器、操作系统和编译器支持的情况下。

不精确意味着有些值无法准确转换为内部格式并以近似值存储，所以存储和检索值可能会显示出细微的差异。管理这些错误以及它们如何在计算中传播是整个数学和计算机科学领域的研究主题，这里不予讨论，只是说明以下几点：

在所有目前受支持的平台上，@{14} 类型的范围大约为 1E-37 到 1E+37，精度至少为 6 位小数。@{15} 类型的范围大约为 1E-307 到 1E+308，精度至少为 15 位。过大或过小的值会引发错误。如果输入数值的精度太高，可能会进行舍入。太接近零的值（不能表示为与零不同）会导致下溢错误。

默认情况下，浮点值以其最短的精确十进制表示形式输出文本；生成的十进制值比真存储的二进制值更接近相同二进制精度中可表示的任何其他值。（但是，输出值目前永远不是 exactly 在两个可表示值之间的中间值，以避免常见错误，即输入例程不正确遵循舍入为最接近偶数的规则。）对于 float8 值，此值至多使用 17 位有效小数位，对于 float4 值，至多使用 9 位有效小数位。

这种最短精度的输出格式生成速度比历史上的四舍五入格式快得多。

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

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

过去，需要明确值应用程序将 extra_float_digits设置为 3 以获取它们。为了尽可能让不同版本兼容，他们应该继续这样做。

除了普通数值以外，浮点类型还有一些特殊值：

Infinity-Infinity_NaN_

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

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

PostgreSQL 还支持 SQL 标准符号 float 和 float(_p)_ 来指定不精确的数值类型。这里，@{28} 指定 binary 位中的最小可接受精度。PostgreSQL 将 float(1) 至 float(24) 作为选择 real 类型的符号，而将 float(25) 至 float(53) 作为选择 double precision 类型。p 值超出允许范围将报错。未指定精度的 float 表示 double precision。

本节介绍一种创建自动增量列的 PostreSQL 特定方式。另一种方式是使用 CREATE TABLE 中描述的 SQL 标准标识列特性。

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

等效于指定：

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

由于_smallserial_, serial_和_bigserial_是用序列实现的，因此即使从未删除任何行，在列中出现的价值序列中可能有“孔”或空白。即使没有将包含该值的列成功插入表列中，序列分配的值仍然被“用完”。例如，如果执行插入事务回滚，则有可能出现这种情况。有关详细信息，请参见 Section 9.17中的_nextval()。

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

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

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