数字类型 ============= 数字类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。\ `表 `__\ 列出了所有可用类型。 .. container:: table :name: DATATYPE-NUMERIC-TABLE **表 数字类型** .. container:: table-contents +------------------+----------+------------------+------------------+ | 名字 | 存储尺寸 | 描述 | 范围 | +==================+==========+==================+==================+ | ``smallint`` | 2字节 | 小范围整数 | -32768 to +32767 | +------------------+----------+------------------+------------------+ | ``integer`` | 4字节 | 整数的典型选择 | -2147483648 to | | | | | +2147483647 | +------------------+----------+------------------+------------------+ | ``bigint`` | 8字节 | 大范围整数 | -922 | | | | | 3372036854775808 | | | | | to | | | | | +922 | | | | | 3372036854775807 | +------------------+----------+------------------+------------------+ | ``decimal`` | 可变 | 用 | 最高小数点 | | | | 户指定精度，精确 | 前131072位，以及 | | | | | 小数点后16383位 | +------------------+----------+------------------+------------------+ | ``numeric`` | 可变 | 用 | 最高小数点 | | | | 户指定精度，精确 | 前131072位，以及 | | | | | 小数点后16383位 | +------------------+----------+------------------+------------------+ | ``real`` | 4字节 | 可变精度，不精确 | 6位十进制精度 | +------------------+----------+------------------+------------------+ | ``do | 8字节 | 可变精度，不精确 | 15位十进制精度 | | uble precision`` | | | | +------------------+----------+------------------+------------------+ | ``smallserial`` | 2字节 | 自动增加的小整数 | 1到32767 | +------------------+----------+------------------+------------------+ | ``serial`` | 4字节 | 自动增加的整数 | 1到2147483647 | +------------------+----------+------------------+------------------+ | ``bigserial`` | 8字节 | 自动增长的大整数 | 1到922 | | | | | 3372036854775807 | +------------------+----------+------------------+------------------+ 数字类型常量的语法在\ `节 `__\ 里描述。数字类型有一整套对应的数学操作符和函数。相关信息请参考 `章 `__\ 。下面的几节详细描述这些类型。 整数类型 -------------- 类型\ ``smallint``\ 、\ ``integer``\ 和\ ``bigint``\ 存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的值将导致一个错误。 常用的类型是\ ``integer``\ ，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 ``smallint``\ 类型。而只有在\ ``integer``\ 的范围不够的时候才使用\ ``bigint``\ 。 SQL只声明了整数类型\ ``integer``\ （或\ ``int``\ ）、\ ``smallint``\ 和\ ``bigint``\ 。类型\ ``int2``\ 、\ ``int4``\ 和\ ``int8``\ 都是扩展，也在许多其它SQL数据库系统中使用。 任意精度数字 -------------- 类型\ ``numeric``\ 可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确的数量。\ ``numeric``\ 值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不过，\ ``numeric``\ 类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。 在随后的内容里，我们使用了下述术语：一个\ ``numeric``\ 的\ *precision*\ （精度）是整个数中有效位的总数，也就是小数点两边的位数。\ ``numeric``\ 的\ *scale*\ （刻度）是小数部分的数字位数，也就是小数点右边的部分。因此数字 5141 的精度为6而刻度为4。可以认为整数的刻度为零。 ``numeric``\ 列的最大精度和最大比例都是可以配置的。要声明一个类型为\ ``numeric``\ 的列，你可以用下面的语法： .. code:: programlisting NUMERIC(precision, scale) 精度必须为正数，比例可以为零或者正数。另外： .. code:: programlisting NUMERIC(precision) 选择比例为 0 。如果使用 .. code:: programlisting NUMERIC 创建一个列时不使用精度或比例，则该列可以存储任何精度和比例的数字值，并且值的范围最多可以到实现精度的上限。一个这种列将不会把输入值转化成任何特定的比例，而带有比例声明的\ ``numeric``\ 列将把输入值转化为该比例（SQL标准要求缺省的比例是 0，即转化成整数精度。我们觉得这样做有点没用。如果你关心移植性，那你最好总是显式声明精度和比例）。 .. note:: .. rubric:: 注意 :name: 注意 :class: title 显式指定类型精度时的最大允许精度为 1000，没有指定精度的\ ``NUMERIC``\ 受到\ `表 `__\ 中描述的限制所控制。 如果一个要存储的值的比例比列声明的比例高，那么系统将尝试圆整（四舍五入）该值到指定的分数位数。 然后，如果小数点左边的位数超过了声明的精度减去声明的比例，那么抛出一个错误。 数字值在物理上是以不带任何前导或者后缀零的形式存储。 因此，列上声明的精度和比例都是最大值，而不是固定分配的 （在这个方面，\ ``numeric``\ 类型更类似于\ ``varchar(n``)， 而不像\ ``char(n``)）。 实际存储要求是每四个十进制位组用两个字节，再加上三到八个字节的开销。 除了普通的数字值之外，\ ``numeric``\ 类型允许特殊值\ ``NaN``\ ， 表示“不是一个数字”。任何在 ``NaN``\ 上面的操作都生成另外一个\ ``NaN``\ 。 如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，例如\ ``UPDATE table SET x = 'NaN'``\ 。在输入时，字串\ ``NaN``\ 被识别为大小写无关。 .. note:: .. rubric:: 注意 :name: 注意-1 :class: title 在“不是一个数字”概念的大部分实现中，\ ``NaN``\ 被认为不等于任何其他数字值（包括\ ``NaN``\ ）。为了允许\ ``numeric``\ 值可以被排序和使用基于树的索引，PostgreSQL把\ ``NaN``\ 值视为相等，并且比所有非\ ``NaN``\ 值都要大。 类型\ ``decimal``\ 和\ ``numeric``\ 是等效的。两种类型都是SQL标准的一部分。 在对值进行圆整时，\ ``numeric``\ 类型会圆到远离零的整数，而（在大部分机器上）\ ``real``\ 和\ ``double precision``\ 类型会圆到最近的偶数上。例如： .. code:: programlisting 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) OushuDB 对用户表的所有存储格式（包括内部表和外部表）的建表语句（包括create as select，create table like）中的不指定精度的`NUMERIC`类型的行为进行重新规范整理（注，为记录方便，下述行文中精度统指precision和scale的组合）。用户可以通过GUC配置不指定精度NUMERIC的默认行为。当不指定精度的NUMERIC的默认行为被通过相关GUC: default_numeric_unconstrained_storage更改为固定精度时，进行建表操作将根据对应列名发出NOTICE，检查表定义可以看见实际上存储的NUMERIC精度。 对于升级操作，上述改动只影响新创建的用户表，存量表按照存量表创建时的表定义确认相应行为。可以通过psql的 \\d+ 用户表 确认表的行为是存储任意精度还是固定精度。 默认装机行为是映射不指定精度的 NUEMRIC 为 NUMERIC(38,15) ，此行为变更了以前沿用Postgres任意精度的默认行为。 GUC default_numeric_unconstrained_storage用于配置不指定精度时的NUMERIC的存储行为。其合法配置形式为NUMERIC和NUMERIC(p,s)。OushuDB对于不符合输入格式和输入范围的配置将进行报错提示以及忽略。客户端中执行show default_numeric_unconstrained_storage;可检查当前行为。该GUC可以通过oushudb-site.xml或者 alter database set to进行配置，建议通过database或者session内set default_numeric_unconstrained_storage进行配置。 OushuDB 各存储格式的精度限制 :: | 内部表 | 外部表 | 固定精度限制 | 任意精度支持 | --- | --- | --- | --- | ORC | | 1~38 | 同Postgres12，以Postgres格式的BINARY转储而不是hive0.11的字符串 | MAGMAAP | | 1~38 | 不支持 | HORC | | 1~38 | 不支持 | | TEXT, CSV | 1~1000 | 同Postgres12 | | ORC | 1~38 | 同ORC内部表 其中 固定精度限制 标明`NUMERIC(p,s)`的参数p的取值范围，任意精度支持 标明`NUMERIC`的实现情况。上述行为均考虑psql的 \\d查阅到的表定义。 :: -- test config set default_numeric_unconstrained_storage to 'NUMERIC(5,2)'; NOTICE: Specifying NUMERIC without any precision or scale creates NUMERIC(5,2). SET set default_numeric_unconstrained_storage to 'NUMERIC'; SET :: set default_numeric_unconstrained_storage to 'NUMERIC(40,2)'; NOTICE: Specifying NUMERIC without any precision or scale creates NUMERIC(40,2). SET create table row_40(num decimal) with(appendonly=true,orientation=row); NOTICE: Specifying num NUMERIC without any precision or scale creates NUMERIC(40,2). CREATE TABLE \d row_40 Append-Only Table "public.row_40" Column | Type | Modifiers --------+---------------+----------- d | numeric(40,2) | Compression Type: None Compression Level: 0 Block Size: 32768 Checksum: f Table Bucket Number: 9 Distributed randomly :: set default_numeric_unconstrained_storage to 'numeric('; WARNING: Must set with `NUMERIC(p,s)` or `NUMERIC` ERROR: invalid value for parameter "default_numeric_unconstrained_storage": "numeric(" set default_numeric_unconstrained_storage to 'dec'; WARNING: Must set with `NUMERIC(p,s)` or `NUMERIC` ERROR: invalid value for parameter "default_numeric_unconstrained_storage": "dec" set default_numeric_unconstrained_storage to 'NUMERIC(1001,0)'; -- invalid upper bound WARNING: NUMERIC precision 1001 must be between 1 and 1000 ERROR: invalid value for parameter "default_numeric_unconstrained_storage": "NUMERIC(1001,0)" set default_numeric_unconstrained_storage to 'NUMERIC(5,7)'; WARNING: NUMERIC scale 7 must be between 0 and 5 ERROR: invalid value for parameter "default_numeric_unconstrained_storage": "NUMERIC(5,7)" 浮点类型 -------------- 数据类型\ ``real``\ 和\ ``double precision``\ 是不精确的、变精度的数字类型。 在所有当前支持的平台上，这些类型是IEEE标准 754 二进制浮点算术（分别对应单精度和双精度）的实现， 一直到下层处理器、操作系统和支持它的编译器。 不准确意味着一些值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储和检索一个值可能出现一些缺失。 处理这些错误以及这些错误是如何在计算中传播的主题属于数学和计算机科学的一个完整的分支， 我们不会在这里进一步讨论它，这里的讨论仅限于如下几点： .. container:: itemizedlist - 如果你要求准确的存储和计算（例如计算货币金额），应使用\ ``numeric``\ 类型。 - 如果你想用这些类型做任何重要的复杂计算，尤其是那些你对范围情况（无穷、下溢）严重依赖的事情，那你应该仔细评诂你的实现。 - 用两个浮点数值进行等值比较不可能总是按照期望地进行。 在所有当前支持的平台上，\ ``real``\ 类型的范围是 1E-37 to 1E+37 ，精度至少是 6 位小数。 ``double precision``\ 类型的范围是 1E-307 to 1E+308 ，精度至少是 15 位数字。 太大或者太小的值都会导致错误。 如果输入数字的精度太高，那么可能发生四舍五入。 太接近零的数字，如果不能体现出与零的区别就会导致下溢错误。 默认情况下，浮点值以其最短精确的十进制表示的文本形式输出；所产生的十进制值与相同二进制精度的任何其他的值表示相比，更接近于真实存储的二进制值。 （但是，当前输出值永远不会\ *精确地*\ 处于两个可表示的值之间，以免输入程序不能正确遵守舍近取整法则。） 对于\ ``float8``\ 值，此值最多使用 17 个有效十进制数字，对于\ ``float4``\ 值，最多使用9个数字。 .. note:: .. rubric:: 注意 :name: 注意-2 :class: title 生成这种最短精确的输出格式比历史的四舍五入的格式要快得多。 为了与PostgreSQL的较旧版本生成的输出兼容，并允许降低输出精度，可以使用\ `extra_float_digits `__\ 参数选择四舍五入的十进制输出。 将值设置为0将恢复以前的默认值，即将值四舍五入为6（对于\ ``float4``\ ）或15（对于\ ``float8``\ ）个有效的十进制数字。 设置负值会进一步减少位数。 例如-2会将输出分别舍入到4或13位数字。 设置\ `extra_float_digits `__\ 位任何大于 0 的值将选择最短精确格式。 .. note:: .. rubric:: 注意 :name: 注意-3 :class: title 需要更精确值的应用需要设置\ `extra_float_digits `__\ 为3以获取更精确值。 为了版本之间的最大兼容性，他们可以继续这样做。 除了普通的数字值之外，浮点类型还有几个特殊值： .. container:: literallayout | | ``Infinity`` | ``-Infinity`` | ``NaN`` 这些分别代表 IEEE 754 特殊值“infinity”、“negative infinity”以及“not-a-number”， 如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号，例如\ ``UPDATE table SET x = '-Infinity'``\ 。 在输入时，这些字符串是以大小写不敏感的方式识别的。 .. note:: .. rubric:: 注意 :name: 注意-4 :class: title IEEE754指定\ ``NaN``\ 不应该与任何其他浮点值（包括\ ``NaN``\ ）相等。为了允许浮点值被排序或者在基于树的索引中使用，PostgreSQL将\ ``NaN``\ 值视为相等，并且比所有非\ ``NaN``\ 值要更大。 PostgreSQL还支持 SQL 标准表示法\ ``float``\ 和\ ``float(p``)用于声明非精确的数字类型。在这里，\ *``p``*\ 指定以\ *二进制*\ 位表示的最低可接受精度。 在选取\ ``real``\ 类型的时候，PostgreSQL接受\ ``float(1)``\ 到\ ``float(24)``\ ，在选取\ ``double precision``\ 的时候，接受\ ``float(25)``\ 到\ ``float(53)``\ 。在允许范围之外的\ *``p``*\ 值将导致一个错误。没有指定精度的\ ``float``\ 将被当作是\ ``double precision``\ 。 序数类型 -------------- .. note:: .. rubric:: 注意 :name: 注意-5 :class: title 这一节描述了PostgreSQL特有的创建一个自增列的方法。另一种方法是使用SQL标准的标识列特性，它在\ `CREATE TABLE `__\ 中描述。 ``smallserial``\ 、\ ``serial``\ 和\ ``bigserial``\ 类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的\ ``AUTO_INCREMENT``\ 属性）。 在目前的实现中，下面一个语句： .. code:: programlisting CREATE TABLE tablename ( colname SERIAL ); 等价于以下语句： .. code:: programlisting 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``\ 约束避免意外地插入重复的值，但这个不是自动发生的）。最后，该序列被标记为“属于”该列，这样当列或表被删除时该序列也会被删除。 .. note:: .. rubric:: 注意 :name: 注意-6 :class: title 因为\ ``smallserial``\ 、\ ``serial``\ 和\ ``bigserial``\ 是用序列实现的，所以即使没有删除过行，在出现在列中的序列值可能有“空洞”或者间隙。如果一个从序列中分配的值被用在一行中，即使该行最终没有被成功地插入到表中，该值也被“用掉”了。例如，当插入事务回滚时就会发生这种情况。更多信息参见\ `节 `__\ 中的\ ``nextval()``\ 。 要使用\ ``serial``\ 列插入序列的下一个数值到表中， 请指定\ ``serial``\ 列应该被赋予其缺省值。我们可以通过在\ ``INSERT``\ 语句中把该列排除在列列表之外来实现，也可以通过使用\ ``DEFAULT``\ 关键字来实现。 类型名\ ``serial``\ 和\ ``serial4``\ 是等效的： 两个都创建\ ``integer``\ 列。类型名\ ``bigserial``\ 和\ ``serial8``\ 也一样，只不过它们创建一个 ``bigint``\ 列。如果你预计在表的生存期中使用的标识符数目超过 2\ :sup:`31` 个，那么你应该使用\ ``bigserial``\ 。类型名\ ``smallserial``\ 和\ ``serial2``\ 也以相同方式工作，只不过它们创建一个\ ``smallint``\ 列。 为一个\ ``serial``\ 列创建的序列在所属的列被删除的时候自动删除。你可以在不删除列的情况下删除序列，但是这会强制删除该列的默认值表达式。