今天一位朋友问我Oracle转换到PostgreSQL时，Oracle的INT应该转换为PostgreSQL的什么类型？
差点被integer这个词迷惑，其实在Oracle中，integer使用NUMBER来存储的，只是不存储小数。
例如：
在sqlplus客户端中，数字长度显示默认为10，超出的话会用科学方法表示，所以不要误以为这是精度问题哦。
精度实际上是NUMBER(38)。超过可能遇到BUG，虽然可以存储进去。例如以下，40个9显示正常，但是41个9时进位了，已经精度不准确。
在PostgreSQL中，我们要使用对应的numeric类型来代替Oracle的int类型，并且非常完美，超出40位没有问题。
SQL> set numwidth 50
SQL> create table test(id int);
Table created.
SQL> insert into test values (9999999999999999999);
1 row created.
SQL> select * from test;
ID
--------------------------------------------------
9999999999999999999在sqlplus客户端中，数字长度显示默认为10，超出的话会用科学方法表示，所以不要误以为这是精度问题哦。
SQL> set numwidth 10
SQL> select * from test;
ID
----------
1.0000E+19精度实际上是NUMBER(38)。超过可能遇到BUG，虽然可以存储进去。例如以下，40个9显示正常，但是41个9时进位了，已经精度不准确。
SQL> insert into test values (9999999999999999999999999999999999999999);
SQL> insert into test values (99999999999999999999999999999999999999999);
SQL> select * from test;
ID
--------------------------------------------------
9999999999999999999999999999999999999999
100000000000000000000000000000000000000000
使用NUMERIC表现一样：
SQL> drop table test;
Table dropped.
SQL> create table test(id numeric);
Table created.
SQL> insert into test values (9999999999999999999999999999999999999999);
SQL> insert into test values (99999999999999999999999999999999999999999);
SQL> select * from test;
ID
--------------------------------------------------
9999999999999999999999999999999999999999
100000000000000000000000000000000000000000在PostgreSQL中，我们要使用对应的numeric类型来代替Oracle的int类型，并且非常完美，超出40位没有问题。
postgres=# select 9999999999999999999999999999999999999999::numeric;
numeric
------------------------------------------
9999999999999999999999999999999999999999
(1 row)
postgres=# select 99999999999999999999999999999999999999999::numeric;
numeric
-------------------------------------------
99999999999999999999999999999999999999999
(1 row)
postgres=# select 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999::numeric;
numeric
-------------------------------------------------------------------------------------------------
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
(1 row)
postgres=# select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999::numeric;
numeric
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
(1 row)
postgres=# select 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999::numeric;
numeric
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
(1 row)
postgres=# select 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888888888888888888888888888888888888::numeric;
num
eric
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
--
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888888888888888888888888888888888888
8
(1 row)
postgres=# create table test(id numeric);
CREATE TABLE
postgres=# insert into test values (9999999999999999999999999999999999999999);
INSERT 0 1
postgres=# insert into test values (99999999999999999999999999999999999999999);
INSERT 0 1
postgres=# select * from test;
id
-------------------------------------------
9999999999999999999999999999999999999999
99999999999999999999999999999999999999999
(2 rows)
postgres=# insert into test values (9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999991111111111111111111111111111111111111111111111111111111111111111111);
INSERT 0 1
postgres=# select * from test;
id
------------------------------------------------------------------------------------------------------------------------------------
------------------------------
99999999999
99999999999999999999999999999
999999999999
99999999999999999999999999999
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999911111111111111111111111111111111111111
11111111111111111111111111111
(3 rows)
那么PostgreSQL的numeric精度有多大呢？
src/include/utils/numeric.h
如果你要限制numeric的精度，那么最大允许1000的长度限制。
/*-------------------------------------------------------------------------
*
* numeric.h
* Definitions for the exact numeric data type of Postgres
*
* Original coding 1998, Jan Wieck. Heavily revised 2003, Tom Lane.
*
* Copyright (c) 1998-2010, PostgreSQL Global Development Group
*
* $PostgreSQL: pgsql/src/include/utils/numeric.h,v 1.29 2010/01/02 16:58:10 momjian Exp $
*
*-------------------------------------------------------------------------
*/
/*
* The Numeric data type stored in the database
*
* NOTE: by convention, values in the packed form have been stripped of
* all leading and trailing zero digits (where a "digit" is of base NBASE).
* In particular, if the value is zero, there will be no digits at all!
* The weight is arbitrary in that case, but we normally set it to zero.
*/
typedef struct NumericData
{
int32 vl_len_; /* varlena header (do not touch directly!) */
uint16 n_sign_dscale; /* Sign + display scale */
int16 n_weight; /* Weight of 1st digit */
char n_data[1]; /* Digits (really array of NumericDigit) */
} NumericData;
typedef NumericData *Numeric;
/*
* Hardcoded precision limit - arbitrary, but must be small enough that
* dscale values will fit in 14 bits.
*/
#define NUMERIC_MAX_PRECISION 1000如果你要限制numeric的精度，那么最大允许1000的长度限制。
但是如果你不限制，允许多大的数字呢？
数字总长度不能超过uint16+uint16+2^14，即131072+16384，其中131072为整数部分，16384为小数点以及小数部分。
postgres=# select 2^16 + 2^16;
?column?
----------
131072
(1 row)
postgres=# select repeat('9',131073)::numeric;
ERROR: 22003: value overflows numeric format
LOCATION: make_result, numeric.c:4202
postgres=# select repeat('9',131072)::numeric;
正常返回
postgres=# select (repeat('9',131072)||'.'||repeat('9',16384))::numeric;
ERROR: 22003: value overflows numeric format
LOCATION: make_result, numeric.c:4202
postgres=# select (repeat('9',131072)||'.'||repeat('9',16383))::numeric;
正常返回
参考，
src/backend/utils/adt/numeric.c
其实有两种格式，一种SHORT，一种LONG。
[参考]
/*
* The Numeric type as stored on disk.
*
* If the high bits of the first word of a NumericChoice (n_header, or
* n_short.n_header, or n_long.n_sign_dscale) are NUMERIC_SHORT, then the
* numeric follows the NumericShort format; if they are NUMERIC_POS or
* NUMERIC_NEG, it follows the NumericLong format. If they are NUMERIC_NAN,
* it is a NaN. We currently always store a NaN using just two bytes (i.e.
* only n_header), but previous releases used only the NumericLong format,
* so we might find 4-byte NaNs on disk if a database has been migrated using
* pg_upgrade. In either case, when the high bits indicate a NaN, the
* remaining bits are never examined. Currently, we always initialize these
* to zero, but it might be possible to use them for some other purpose in
* the future.
*
* In the NumericShort format, the remaining 14 bits of the header word
* (n_short.n_header) are allocated as follows: 1 for sign (positive or
* negative), 6 for dynamic scale, and 7 for weight. In practice, most
* commonly-encountered values can be represented this way.
*
* In the NumericLong format, the remaining 14 bits of the header word
* (n_long.n_sign_dscale) represent the display scale; and the weight is
* stored separately in n_weight.
*
* NOTE: by convention, values in the packed form have been stripped of
* all leading and trailing zero digits (where a "digit" is of base NBASE).
* In particular, if the value is zero, there will be no digits at all!
* The weight is arbitrary in that case, but we normally set it to zero.
*/
struct NumericShort
{
uint16 n_header; /* Sign + display scale + weight */
NumericDigit n_data[1]; /* Digits */
};
struct NumericLong
{
uint16 n_sign_dscale; /* Sign + display scale */
int16 n_weight; /* Weight of 1st digit */
NumericDigit n_data[1]; /* Digits */
};
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) */
};
struct NumericData
{
int32 vl_len_; /* varlena header (do not touch directly!) */
union NumericChoice choice; /* choice of format */
};/*
* make_result() -
*
* Create the packed db numeric format in palloc()'d memory from
* a variable.
*/
static Numeric
make_result(NumericVar *var)
{
Numeric result;
NumericDigit *digits = var->digits;
int weight = var->weight;
int sign = var->sign;
int n;
Size len;
if (sign == NUMERIC_NAN)
{
result = (Numeric) palloc(NUMERIC_HDRSZ_SHORT);
SET_VARSIZE(result, NUMERIC_HDRSZ_SHORT);
result->choice.n_header = NUMERIC_NAN;
/* the header word is all we need */
dump_numeric("make_result()", result);
return result;
}
n = var->ndigits;
/* truncate leading zeroes */
while (n > 0 && *digits == 0)
{
digits++;
weight--;
n--;
}
/* truncate trailing zeroes */
while (n > 0 && digits[n - 1] == 0)
n--;
/* If zero result, force to weight=0 and positive sign */
if (n == 0)
{
weight = 0;
sign = NUMERIC_POS;
}
/* Build the result */
if (NUMERIC_CAN_BE_SHORT(var->dscale, weight))
{
len = NUMERIC_HDRSZ_SHORT + n * sizeof(NumericDigit);
result = (Numeric) palloc(len);
SET_VARSIZE(result, len);
result->choice.n_short.n_header =
(sign == NUMERIC_NEG ? (NUMERIC_SHORT | NUMERIC_SHORT_SIGN_MASK)
: NUMERIC_SHORT)
| (var->dscale << NUMERIC_SHORT_DSCALE_SHIFT)
| (weight < 0 ? NUMERIC_SHORT_WEIGHT_SIGN_MASK : 0)
| (weight & NUMERIC_SHORT_WEIGHT_MASK);
}
else
{
len = NUMERIC_HDRSZ + n * sizeof(NumericDigit);
result = (Numeric) palloc(len);
SET_VARSIZE(result, len);
result->choice.n_long.n_sign_dscale =
sign | (var->dscale & NUMERIC_DSCALE_MASK);
result->choice.n_long.n_weight = weight;
}
memcpy(NUMERIC_DIGITS(result), digits, n * sizeof(NumericDigit));
Assert(NUMERIC_NDIGITS(result) == n);
/* Check for overflow of int16 fields */
if (NUMERIC_WEIGHT(result) != weight ||
NUMERIC_DSCALE(result) != var->dscale)
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("value overflows numeric format")));
dump_numeric("make_result()", result);
return result;
}| numeric | variable | user-specified precision, exact | no limit |
3.
src/include/utils/numeric.h
