数值类型 ========== 数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。下表列出了所有可用类型。 **表.数据类型** .. list-table:: :widths: auto :header-rows: 1 * - 名字 - 存储长度 - 描述 - 范围 * - smallint - 2 字节 - 小范围整数 - -32768 到 +32767 * - integer - 4 字节 - 常用的整数 - -2147483648 到 +2147483647 * - bigint - 8 字节 - 大范围整数 - -9223372036854775808 到 +9223372036854775807 * - decimal - 可变长 - 用户指定的精度，精确 - 小数点前 131072 位；小数点后 16383 位 * - numeric - 可变长 - 用户指定的精度，精确 - 小数点前 131072 位；小数点后 16383 位 * - real - 4 字节 - 可变精度，不精确 - 6 位十进制数字精度 * - double precision - 8 字节 - 可变精度，不精确 - 15 位十进制数字精度 * - smallserial - 2 字节 - 自增的小范围整数 - 1 到 32767 * - serial - 4 字节 - 自增整数 - 1 到 2147483647 * - bigserial - 8 字节 - 自增的大范围整数 - 1 到 9223372036854775807 数值类型常量的语法在 `常量 <./sql-lexical-structure.html#id4>`_ 里描述。 对应数值类型有一套完整的数学操作符和函数。相关信息请参考 `函数和操作符 <./functions-and-operators.html>`_ 。 下面详细描述这些类型。 整数类型 ---------- smallint，integer和bigint类型存储各种取值范围的整数， 即，没有小数部分的数字。存储超出取值范围以外的数值将会报错。 常用的类型是integer，因为它在取值范围、存储空间、性能之间提供了最佳的平衡。 一般只有在磁盘空间紧张的时候才使用smallint。 当integer的取值范围不够用的时候才使用bigint，因为前者相当快。 bigint类型或许不能在所有平台上都能使用，因为它依赖于编译器支持八字节整数。在没有此类支持的机器上，bigint和integer表现得一样(但是它依旧占用存储的八个字节)。 SQL标准只定义了整型integer(或int)和smallint，bigint类型，而int2, int4 和int8都是扩展名，这些扩展名也在许多其它符合SQL标准的数据库系统中使用。 任意精度数值 --------------- numeric类型最多可以存放1000个数字的数值并且精准地计算。我们特别建议将它用于货币金额和其它要求精确计算的场合。不过， numeric类型上的算术运算比整数类型或者我们下一节描述的浮点数类型要慢很多。 在随后的内容里，我们使用下述术语：一个numeric类型的标度 (scale)是小数部分的位数，精度(precision) 是全部数据位的数目，也就是小数点两边的位数总和。 因此数字 23.5141 的精度为 6 而标度为 4 。你可以认为整数的标度为零。 numeric列的最大精度和最大标度都是可以配置的。 要声明一个列的类型为numeric，你可以用下面的语法： :: NUMERIC(precision, scale) 精度必须为正数，标度可以为零或者正数。另外: :: NUMERIC(precision) 选择了标度为 0 。不带任何精度与标度的声明: :: NUMERIC 则创建一个可以存储一个直到实现精度上限的任意精度和标度的数值， 一个这样类型的列将不会把输入数值转化成任何特定的标度， 而带有标度声明的numeric列将把输入值转化为该标度。 SQL标准要求缺省的标度是 0(也就是转化成整数精度)。 我们觉得这样做有点没用。如果你关心移植性， 那你最好总是明确声明精度和标度。 numeric 类型的数据值在物理上是不带任何前导或者后缀零的形式存储的。 因此，列上声明的精度和标度都是最大值，而不是固定分配的。在这个方面， numeric类型更类似于varchar(n)而不是 char(n)。实际存储是每四个十进制位两个字节， 然后在整个数据上加上八个字节的额外开销。 类型decimal和numeric是等效的。 两种类型都是SQL标准。 任意精度数值 -- OushuDB 5.2 :::::::::::::::::::::::::::::::::::::::: OushuDB 5.2对用户表的所有存储格式（包括内部表和外部表）的建表语句（包括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 5.2 各存储格式的精度限制 :: | 内部表 | 外部表 | 固定精度限制 | 任意精度支持 | --- | --- | --- | --- | ROW | | 1~1000 | 同Postgres 8.2.15 | ORC | | 1~38 | 同Postgres 8.2.15，以Postgres格式的BINARY转储而不是hive0.11的字符串 | MAGMAAP | | 1~38 | 不支持 | HORC | | 1~38 | 不支持 | | TEXT, CSV | 1~1000 | 同ROW内部表 | | 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标准二进制浮点数算术(分别对应单和双精度)的一般实现， 外加下层处理器、操作系统和编译器对它的支持。 不精确意味着一些数值不能精确地转换成内部格式并且是以近似值存储的， 因此存储后再把数据打印出来可能有一些差异。 处理这些错误以及这些错误是如何在计算中传播的属于数学和计算机科学的一个完整的分支， 我们不会在这里进一步讨论它，这里的讨论仅限于如下几点： * 如果你要求精确的计算(比如计算货币金额)，应使用numeric类型。 * 如果你想用这些类型做任何重要的复杂计算， 尤其是那些你对范围情况(无穷/下溢)严重依赖的计算，那你应该仔细评诂你的实现。 * 拿两个浮点数值进行相等性比较可能不像你想像那样运转。 在大多数平台上，real类型的范围是至少1E-37到1E+37， 精度至少是6位小数。double precision的范围通常是1E-307到1E+308， 精度是至少15位数字。太大或者太小的数值都会导致错误。如果输入数据的精度太高， 那么将会发生圆整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错误。 除了普通的数字值之外，浮点类型还有几个特殊值： Infinity -Infinity 这些值分别表示 IEEE 754 特殊值"正无穷大"、"负无穷大"。在不遵循 IEEE 754 浮点算术的机器上， 这些值的含义可能不是预期的。如果在 SQL 命令里把这些数值当作常量写， 你必须在它们周围放上单引号，像这样：UPDATE table SET x = 'Infinity'。 输入时，这些值是以大小写不敏感的方式识别的。 OushuDB 还支持 SQL 标准表示法 float 和 float(p) 用于声明非精确的数值类型。 其中的 p 声明以二进制位表示的最低可接受精度。 在选取 real 类型的时候，OushuDB 接受 float(1) 到 float(24)，在选取 double precision 的时候，接受 float(25) 到 float(53)。 在允许范围之外的 p 值将导致一个错误。 没有声明精度的 float 将被当作 double precision。 序列号类型 ----------- serial和bigserial类型不是真正的类型， 只是为在表中创建唯一标识做的概念上的便利。类似其它一些数据库中的AUTO_INCREMENT 属性。在目前的实现中，下面一个语句： :: CREATE TABLE tablename ( colname SERIAL ); 等价于声明下面几个语句： :: CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; 因此，我们就创建了一个整数列并且把它的缺省数值安排为从一个序列发生器读取。 应用了一个NOT NULL约束以确保 NULL 不会被插入。 在大多数情况下你可能还希望附加一个UNIQUE或PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动的。最后， 将序列发生器"从属于"那个列，这样当该列或表被删除的时候也一并删除它。 要在serial列中插入序列中的下一个数值，主要是要注意serial 列应该赋予缺省值。我们可以通过在INSERT 语句中把该列排除在表中列的列表之外来实现，也可以通过使用DEFAULT关键字来实现。 类型名serial和serial4是等效的： 两者都创建类型为integer的列。类型名bigserial和serial8 也一样，只不过它创建一个类型为bigint的列。 如果你预计在表的生存期中使用的标识数目可能超过 2^31（4294967296） 个， 那么你应该使用bigserial。 一个serial类型创建的序列在所属的列被删除的时候自动删除。 你可以只删除序列而不删除列，不过这将删除该列的缺省值表达式。