Pay attention: Oracle INTEGER is NUMBER(p) not INT4 in PostgreSQL

今天一位朋友问我Oracle转换到PostgreSQL时，Oracle的INT应该转换为PostgreSQL的什么类型？

差点被integer这个词迷惑，其实在Oracle中，integer使用NUMBER来存储的，只是不存储小数。

例如： 

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.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

[参考]
1. http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF00213

2. http://www.postgresql.org/docs/9.0/static/datatype-numeric.html

3. src/include/utils/numeric.h

时间： 2024-08-04 08:46:08

Pay attention: Oracle INTEGER is NUMBER(p) not INT4 in PostgreSQL的相关文章

oracle中字段NUMBER(15,5)显示到前台就变成6位小数了

问题描述 oracle中字段NUMBER(15,5)显示到前台就变成6位小数了,查看过没有经过特殊处理,直接select*,然后datebind请大侠指点迷津 解决方案 解决方案二:用Number(15,4)解决方案三:没实际用过oracle帮顶解决方案四:你在数据库中查询时,是几位,也是六位吗

SQL Server 2005与Oracle同步 注意NUMBER类型转换

在前面的文章中我已经讲到使用同义词的方法来在SQL Server 2005下连接Oracle,我们可以使用同义 词在SQL Server 2005下连接Oracle来实时访问Oracle数据库,但是如果Oracle中的表数据流较大则会影 响应用系统的性能,于是应采用数据库作业每天定时执行: drop table abc--删除旧表 go select * into abc from aaa--aaa为同义词 from aaa--aaa为同义词这样就可以把Oracle中的数据同步到本地的SQL S

SQL Server 2005与Oracle同步注意NUMBER类型转换

在前面的文章中我已经讲到使用同义词的方法来在SQL Server 2005下连接Oracle,我们可以使用同义词来实时访问Oracle数据库,但是如果Oracle中的表数据流较大则会影响应用系统的性能,于是我采用数据库作业每天定时执行:  drop table abc--删除旧表goselect * into abcfrom aaa--aaa为同义词 这样就可以把Oracle中的数据同步到本地的SQL Server数据库中.从而解决跨实例查询的性能问题.使用这样的方式半年了都没有发现有什么问题,

Oracle中的number类型

number类型用于定义固定长度的数字,可以使整数,也可以是实数.number(p,s)是完整的定义形式. p必须是整数,取值范围是1-38,用于指定数字的总的位数.s必须是整数,取值范围是-84~127,用于指定小数点之后的位数. NUMBER 赋值:1234.56 实际:1234.56 NUMBER(3) 赋值:-123 实际:-123 NUMBER(3) 赋值:1234 实际:错误(ORA-06502:PL/SQL:数字或值错误:数字精度太高) NUMBER(4,3) 赋值:1.23456

[20150503]关于oracle的number类型.txt

[20150503]关于oracle的number类型.txt --节前的事情,别人建表使用number类型,本来想定义成number(10,2),结果少输入0,变成number(1,2). --在我的记忆里,好像前面的数值应该大于后面的精度的,没想到这样竟然可以通过,自己感到很奇怪! --测试下来,才知道自己oracle基本的东西都不是很清楚. 1.首先提到我以前写的一篇blog: [20140823]在sqlplus使用copy注意.txt http://blog.itpub.net/267

使用BenchmarkSQL 测试Oracle 12c TPC-C 性能

使用BenchmarkSQL测试一下Oracle 12c的TPC-C性能,同时对比一下PostgreSQL 9.5的性能. 测试机用的是FusionIO卡,24核的CPU,192G内存. 12c安装请参考 http://blog.163.com/digoal@126/blog/static/1638770402015112281556942/ http://blog.163.com/digoal@126/blog/static/163877040201511232138735/ 下载benchm

ORACLE基本数据类型总结

     ORACLE基本数据类型(亦叫内置数据类型 built-in datatypes)可以按类型分为:字符串类型.数字类型.日期类型.LOB类型.LONG RAW& RAW类型.ROWID & UROWID类型. 在讲叙字符串类型前,先要讲一下编码.字符串类 型的数据可依编码方式分成数据库字符集(CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB) 两种.数据库中的字符串数据都通过字符集将字符转换为数字后(二进制),才存储到数据块

BenchmarkSQL 测试Oracle 12c TPC-C 性能

使用BenchmarkSQL测试一下Oracle 12c的TPC-C性能,同时对比一下PostgreSQL 9.5的性能. 测试机: 3 * PCI-E SSD,逻辑卷条带,XFS,数据块对齐,16核开HT,256G内存. 12c安装,配置请参考 http://blog.163.com/digoal@126/blog/static/1638770402015112281556942/ http://blog.163.com/digoal@126/blog/static/1638770402015

通过ASP.NET连接Oracle数据库实例教程

通过ASP.NET连接Oracle数据库实例教程 长期以来,我一直用的是 MS SQL Server / Access 数据库,通过.NET 访问MS自家的东西几乎没碰到过什么麻烦.最近项目中要用 Oracle 作为数据库,学习研究了一些 .NET 访问Oracle 的东西,发现问题倒真的不少. 1.System.Data.OracleClient 和 System.Data.OleDb 命名空间 虽然通过这两个命名空间的类都可以访问 Oracle 数据库,但和 SQL Server 类似的(S