Postgresql 中文操作指南

8.1. Numeric Types #

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

Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals. Table 8.2 lists the available types.

Table 8.2. Numeric Types

Name

Storage Size

Description

Range

smallint

2 bytes

small-range integer

-32768 to +32767

integer

4 bytes

typical choice for integer

-2147483648 to +2147483647

bigint

8 bytes

large-range integer

-9223372036854775808 to +9223372036854775807

decimal

variable

user-specified precision, exact

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

numeric

variable

user-specified precision, exact

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

real

4 bytes

variable-precision, inexact

6 decimal digits precision

double precision

8 bytes

variable-precision, inexact

15 decimal digits precision

smallserial

2 bytes

small autoincrementing integer

1 to 32767

serial

4 bytes

autoincrementing integer

1 to 2147483647

bigserial

8 bytes

large autoincrementing integer

1 to 9223372036854775807

Section 4.1.2 中介绍了数字类型的常量的语法。数字类型有一组完整对应的算术运算符和函数。有关更多信息，请参阅 Chapter 9。以下各节详细介绍了这些类型。

The syntax of constants for the numeric types is described in Section 4.1.2. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to Chapter 9 for more information. The following sections describe the types in detail.

8.1.1. Integer Types #

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

The types smallint, integer, and bigint store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.

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

The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type is designed to be used when the range of the integer type is insufficient.

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

SQL only specifies the integer types integer (or int), smallint, and bigint. The type names int2, int4, and int8 are extensions, which are also used by some other SQL database systems.

8.1.2. Arbitrary Precision Numbers #

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

The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with numeric values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on numeric values are very slow compared to the integer types, or to the floating-point types described in the next section.

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

We use the following terms below: The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

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

Both the maximum precision and the maximum scale of a numeric column can be configured. To declare a column of type numeric use the syntax:

NUMERIC(precision, scale)

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

The precision must be positive, while the scale may be positive or negative (see below). Alternatively:

NUMERIC(precision)

选择小数位数 0。指定：

selects a scale of 0. Specifying:

NUMERIC

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

without any precision or scale creates an “unconstrained numeric” column in which numeric values of any length can be stored, up to the implementation limits. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you’re concerned about portability, always specify the precision and scale explicitly.)

Note

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

The maximum precision that can be explicitly specified in a numeric type declaration is 1000. An unconstrained numeric column is subject to the limits described in Table 8.2.

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

If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised. For example, a column declared as

NUMERIC(3, 1)

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

will round values to 1 decimal place and can store values between -99.9 and 99.9, inclusive.

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

Beginning in PostgreSQL 15, it is allowed to declare a numeric column with a negative scale. Then values will be rounded to the left of the decimal point. The precision still represents the maximum number of non-rounded digits. Thus, a column declared as

NUMERIC(2, -3)

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

will round values to the nearest thousand and can store values between -99000 and 99000, inclusive. It is also allowed to declare a scale larger than the declared precision. Such a column can only hold fractional values, and it requires the number of zero digits just to the right of the decimal point to be at least the declared scale minus the declared precision. For example, a column declared as

NUMERIC(3, 5)

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

will round values to 5 decimal places and can store values between -0.00999 and 0.00999, inclusive.

Note

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

PostgreSQL permits the scale in a numeric type declaration to be any value in the range -1000 to 1000. However, the SQL standard requires the scale to be in the range 0 to precision. Using scales outside that range may not be portable to other database systems.

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

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(_n)_ than to char(_n)_.) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

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

In addition to ordinary numeric values, the numeric type has several special values:

Infinity-Infinity_NaN_

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

These are adapted from the IEEE 754 standard, and represent “infinity”, “negative infinity”, and “not-a-number”, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example UPDATE table SET x = '-Infinity'. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled inf and -inf.

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

The infinity values behave as per mathematical expectations. For example, Infinity plus any finite value equals Infinity, as does Infinity plus Infinity; but Infinity minus Infinity yields NaN (not a number), because it has no well-defined interpretation. Note that an infinity can only be stored in an unconstrained numeric column, because it notionally exceeds any finite precision limit.

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

The NaN (not a number) value is used to represent undefined calculational results. In general, any operation with a NaN input yields another NaN. The only exception is when the operation’s other inputs are such that the same output would be obtained if the NaN were to be replaced by any finite or infinite numeric value; then, that output value is used for NaN too. (An example of this principle is that NaN raised to the zero power yields one.)

Note

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

In most implementations of the “not-a-number” concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

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

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

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

When rounding values, the numeric type rounds ties away from zero, while (on most machines) the real and double precision types round ties to the nearest even number. For example:

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. Floating-Point Types #

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

The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

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

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

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

On all currently supported platforms, the real type has a range of around 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

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

By default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never exactly midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-nearest-even rule.) This value will use at most 17 significant decimal digits for float8 values, and at most 9 digits for float4 values.

Note

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

This shortest-precise output format is much faster to generate than the historical rounded format.

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

For compatibility with output generated by older versions of PostgreSQL, and to allow the output precision to be reduced, the extra_float_digits parameter can be used to select rounded decimal output instead. Setting a value of 0 restores the previous default of rounding the value to 6 (for float4) or 15 (for float8) significant decimal digits. Setting a negative value reduces the number of digits further; for example -2 would round output to 4 or 13 digits respectively.

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

Any value of extra_float_digits greater than 0 selects the shortest-precise format.

Note

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

Applications that wanted precise values have historically had to set extra_float_digits to 3 to obtain them. For maximum compatibility between versions, they should continue to do so.

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

In addition to ordinary numeric values, the floating-point types have several special values:

Infinity-Infinity_NaN_

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

These represent the IEEE 754 special values “infinity”, “negative infinity”, and “not-a-number”, respectively. When writing these values as constants in an SQL command, you must put quotes around them, for example UPDATE table SET x = '-Infinity'. On input, these strings are recognized in a case-insensitive manner. The infinity values can alternatively be spelled inf and -inf.

Note

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

IEEE 754 specifies that NaN should not compare equal to any other floating-point value (including NaN). In order to allow floating-point values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

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

PostgreSQL also supports the SQL-standard notations float and float(_p)_ for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision. Values of p outside the allowed range draw an error. float with no precision specified is taken to mean double precision.

8.1.4. Serial Types #

Note

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

This section describes a PostgreSQL-specific way to create an autoincrementing column. Another way is to use the SQL-standard identity column feature, described at CREATE TABLE.

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

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

等效于指定：

is equivalent to specifying:

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 约束，以防止无意中插入重复值，但这并不是自动的。）最后，序列被标记为“由”列“拥有”，所以如果列或表被删除，它也会被删除。

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as “owned by” the column, so that it will be dropped if the column or table is dropped.

Note

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

Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.17 for details.

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

To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word.

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

The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type names smallserial and serial2 also work the same way, except that they create a smallint column.

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

The sequence created for a serial column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.