526互联

PostgreSQL NUMERIC 数据类型

发布时间 2023-07-09 20:42:30作者: sahara-随笔

基本介绍

NUMERIC类型的语法：
NUMERIC(precision, scale)
precision 表示整个数据长度，scale 表示小数部分的长度。如： 1234.567 ，precision 为 7 ，scale 为 3.
NUMERIC 类型 在小数点前面长度可达到 **131,072 **，小数点后面长度可达到 16,383。scale >= 0，下面示例表示 scale 为 0：
NUMERIC(precision)
如果 precision 和 scale 都忽略，则可以存储 任何上面提及限制内的长度和精度。
如果一个要存储的数值的标度比字段声明的标度高，那么系统将尝试圆整(四舍五入)该数值到指定的小数位。然后，如果小数点左边的数据位数超过了声明的精度减去声明的标度，那么将抛出一个错误。
NUMERIC
在 PostgreSQL中 NUMERIC 和 DECIMAL 是等价的，两者都是SQL标准的一部分。如果精度不是必须的，则不应选择 NUMERIC，因为计算 NUMERIC 要 比 integer ，float ，double 慢。

数据结构

Numeric的数据结构在磁盘上和在内存中是不同的。在磁盘上存储效率较高，而在内存中读取效率较高。每次从磁盘加载到内存需要先进行结构的转换，存储时也要进行转换。因此，我们将分别对两种数据结构进行说明。

1.内存中的实现

在内存中的数据结构如图1所示：

typedef struct NumericVar
{
	int			ndigits;		/* # of digits in digits[] - can be 0! */
	int			weight;			/* weight of first digit */
	int			sign;			/* NUMERIC_POS, _NEG, _NAN, _PINF, or _NINF */
	int			dscale;			/* display scale */
	NumericDigit *buf;			/* start of palloc'd space for digits[] */
	NumericDigit *digits;		/* base-NBASE digits */
} NumericVar;

首先要知道比较重要的一个宏定义NBASE，定义值为10000，表示单个digit的范围为0-9999。由于用两个字节(int16)可以覆盖这个范围，所以digits的类型NumericDigit使用了int16。
数字计算公式
numeric = sign * (digit0 * NBASE^weight + digit1 * NBASE^(weight - 1) + ... + digitn * NBASE^(weight - ndigits))

2.磁盘上的实现

磁盘上的实现分为两种，一种是short型，一种是long类型，short类型与long类型的结构相似，但占用空间长度要短。在精度要求不是特别高的情况下，用short类型可以节约存储空间。

struct NumericShort
{
	uint16		n_header;		/* Sign + display scale + weight */
	NumericDigit n_data[FLEXIBLE_ARRAY_MEMBER]; /* Digits */
};

struct NumericLong
{
	uint16		n_sign_dscale;	/* Sign + display scale */
	int16		n_weight;		/* Weight of 1st digit	*/
	NumericDigit n_data[FLEXIBLE_ARRAY_MEMBER]; /* Digits */
};

FLEXIBLE_ARRAY_MEMBER是宏定义，值为空，所以此处的n_data也为柔性数组。两种不同的数据结构头部每个位的表示含义如下。

图 3 两种数据结构头部含义

NumericShort中的n_header存储符号位sign、小数位数display scale、权重weight，以及最高位的两位有特殊含义，含义如下表所示。
NumericLong和NumericShort的区别在于，多花了两个字节的空间存储。联系两个结构体的方式是有一个联合体存在，联合体可以根据n_header的最高两位来判断numeric的类型，用不同的方式去读取后面实际存储的数据。

最高两位 表示含义
00 类型为long，且符号为正
01 类型为long，且符号为负
10 类型为short，符号看第3位
11 类型为NaN

除了上面提到的数据结构，还有一个数据结构，包含了一个头部和联合体，如下图所示。vl_len_是数据总长度，值为(4+2+ndigitssizeof(uint16))<<2，4是vl_len_自身的字节数，2位n_header的字节数，ndigitssizeof(uint16)为n_data的字节数，左偏两位是由于varlena的特殊结构，需要空出两位去存储其他信息。Numeric被定义为NumericData*，即一个指针指向存储的位置。

struct NumericData
{
	int32		vl_len_;		/* varlena header (do not touch directly!) */
	union NumericChoice choice; /* choice of format */
};
union NumericChoice
{
	uint16		n_header;		/* Header word */
	struct NumericLong n_long;	/* Long form (4-byte header) */
	struct NumericShort n_short;	/* Short form (2-byte header) */
};

3.数据举例

我们来看一个数字，通过其在两种数据结构下的实际存储来直观感受numeric的实现方式。对于数字12345.06789，其有效数字有10位，小数有5位，在内存中用NumericVar实现的方式为：

ndigits: 4
weight: 1
sign: 0
dscale: 5
digits: 0001 2345 0678 9000

由于整数部分有5位，所以最高位的digit为1，权重weight为1，表示1的权重为104，第二个digit存储数字2345，这两位一起表示整数部分。小数部分有5位，所以dscale为5，用两位digit表示，分别为0678和9000。一共使用了4位digits，所以ndigits为4，sign为0表示这个数字为正数。

这个数字在磁盘上实现的格式应该是：
n_header: 33409(1000 0010 1000 0001)二进制格式
n_data: 0001 2345 0678 9000
n_header最高二位的10表示这是一个NumericShort类型，第三位sign为0，接下来为000101，表示dscale为5，0000001表示weight为1，之所以没有存储ndights是因为这个数字可以根据结构体的大小（因为含有柔型数组）减去头部大小而计算得到，在实际的代码中也是这样做的。n_data即为NumericVar中的digits直接复制而来，完全相同。在这里，vl_len_的值为56，也即（4+2+4*2)<<2。 ndights = ((vl_len_>>>> 2) & 0x3FFFFFFF - (4 + 2 + (NUMERIC_HEADER_IS_SHORT(n) ? 0 : sizeof(int16))) / sizeof(int16)
vl_len_对应(varattrib_4b *) (PTR))->va_4byte.va_header，低2位被预留其他用处，只取高位30位。
#define NUMERIC_NDIGITS(num) ((VARSIZE(num) - NUMERIC_HEADER_SIZE(num)) / sizeof(NumericDigit))

4.精度分析

根据numeric的数据结构，我们可以由此分析一下文档中所写的高精度实现原理。小数点前 131072 位、小数点后 16383 位是被dscale和weight限制的，超出精度范围会报错。
对于long，dscale用14位表示，所以小数点后的位数位16383位（11 1111 1111 1111），也即14位2进制的最大值。weight用一个int16表示，最大值为32767，因此最多可以有(32767+1)*4=131072位。
这种表示方法是一种程序员和机器理解、效率和精度之间的平衡。用十进制方式表示，效率并不高，本来一个int16类型的变量可以表示032767，但只用来表示09999。不过相对于二进制表示程序员和代码阅读者便于理解，使用方便。用十进制另一个好处是可以精确表示，不会出现精度损失的问题。
注意这里weight可以为负数，如果把weight当作无符号整数，表示范围会更大。但这样的话weight没有负数的表示范围，如果数字很小需要很多0占用digits的空间，可能效率会很低。

参考链接
PostgreSQL NUMERIC 数据类型_numeric
PostgreSQL: Numeric类型介绍 ——PostgreSQL源码分析