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

最新推荐文章于 2020-11-30 16:33:59 发布
最新推荐文章于 2020-11-30 16:33:59 发布
阅读量166 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/15149
版权
今天一位朋友问我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;
}


numericvariableuser-specified precision, exactno limit

[参考]
3.  src/include/utils/numeric.h
weixin_34088838
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle类型介绍,oracle 数据类型介绍
weixin_29894211的博客
04-03 391
ORACLEbigint 8 NUMBER(19)binary 50RAW(50)bit1NUMBER(2)char 10...
java.lang.NumberFormatException 异常 int value overflow ：int值溢出
伟哥
04-01 2548
解决这个问题就要从java中short、int、long、float、double的取值范围来说： 基本类型：short二进制位数：16 最小值：Short.MIN_VALUE=-32768 （-2的15此方） 最大值：Short.MAX_VALUE=32767 （2的15次方-1） 基本类型：int 二进制位数：32 包装类：java.lang.Integer 最小值：Integer.MI...
oracle学习（四）--数字型number
archer的技术故事
06-01 441
oracle学习（四）--数字型number oracle中的数字型，推荐使用number。例如：number(2)或者number(5,2)或者number(5,-2)。number定义分别表示精度和小数点附近的精度。 oracle中精度的意思就是可以标识数据精确度的位数。例如：45123.568，它的精度为8，因为有8位数字做出了精确度贡献。321000，它的精确度是3，因为有3位
PostgreSQL常用数据类型
11-17
PostgreSQL数据类型，以及与oracle数据类型的对应
PostgreSQL .数据类型普通数据类型
m0_51063580的博客
11-30 3150
PostgreSQL有着丰富的本地数据类型可用。用户可以使用CREATE TYPE命令为 PostgreSQL增加新的数据类型。 表8.1显示了所有内建的普通数据类型。大部分在“别名”列里列出的可选名字都是因历史原因 被PostgreSQL在内部使用的名字。另外，还有一些内部使用的或者废弃的类型也可以用，但没有在这里列出。 表8.1.数据类型 名字 别名 描述 bigint int8 有符号的8字节整数 bigserial serial8 自动增长的8...
code2pg：帮助将应用程序代码从Oracle迁移到PostgreSQL工具
02-05
code2pg是一款强大的工具，专为那些希望将他们的应用程序代码从Oracle数据库系统迁移至PostgreSQL数据库系统的开发者设计。这个工具的出现，旨在简化这个过程，减少手动操作带来的复杂性和潜在错误，提高迁移效率。 ...
oci-postgresql:Terraform模块可在Oracle Cloud Infrastructure（OCI）上部署PostgreSQL
03-22
oci-postgresql PostgreSQL是一个开放源代码的对象关系数据库管理系统。 它具有高度可扩展性，高度可扩展性，并具有许多功能。 PostgreSQL支持跨多个数据中心的数据复制。 此参考体系结构显示了在Oracle Cloud ...
Ora2PgDump:Oracle 到 PostgreSQL 转储
06-10
Oracle 到 PostgreSQL 转储 此实用程序来自需要以合理的速度将 Oracle 数据转换为 PostgreSQL 数据。 这个项目相当实用，但它是一个开始。 它不创建表； 目标表必须已经存在。 目标列名称必须与查询列名称匹配（列...
dbForge.Studio.for(mysql,sql,Oracle,PostgreSQL)
最新发布
12-08
dbForge Studio 是一系列高效、全面的数据库开发和管理工具，专为MySQL、SQL Server、Oracle以及PostgreSQL数据库设计。这些工具旨在提升数据库专业人员的工作效率，提供一体化的环境来完成从设计、创建到优化和监控...
Postgresql连接oracle驱动并实现读写
07-13
### Postgresql连接Oracle数据库并通过oracle_fdw驱动实现读写操作 #### 一、概述 本文档将详细介绍如何在CentOS 6.2环境下通过PostgreSQL 9.5.6使用`oracle_fdw`驱动程序来连接Oracle 11.2客户端，并实现数据的...
《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列二：数字类型
HighGO
01-30 4672
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先遇到的，并且也是最重要的，就是数据类型之间的转换。下面根据自己的理解和测试，写了一些数据类型之间的差异以及迁移时的注意事项的文章，不足之处，尚请多多指教。 数
从 Oracle 到 PostgreSQL ，乐动体育官方保险公司迁移实践
weixin_45812222的博客
11-09 859
Oracle ACE-A，Oracle 10g OCM，SOUG（中国南方ORACLE用户组）联合发起人，ITPUB 专家。目前任职于某公司，担任技术经理职位，负责公司的软件基础设施的建设和维护工作。 PostgreSQL VS Oracle 站在开发和应用的角度看，乐动体育官方的LD90.VIP底层数据库搭建再好，但如果应用在上面跑不起来，跑的不好，新数据库平台就会变成一座鬼城。 今天，我分享的...
numeric overflow occurred数值溢出
someInNeed的博客
09-21 3686
如果是整数型就cast成bigint 如果是浮点型就是小数点后数目不够
Format - Numeric
weixin_30865427的博客
08-08 257
1. 一些常用格式，参考链接：http://msdn.microsoft.com/en-us/library/0c899ak8(v=vs.110).aspx int value = -1234; Console.WriteLine(value.ToString("D8")); double double...
PostgreSql:用语句修改字段报“numeric field overflow”异常
qq_37213573的博客
08-22 1万+
PostgreSql:修改表字段报“numeric field overflow”问题 今天在navicat客户端用语句修改某个表字段长度时，发生了“numeric field overflow”异常 解决： 直接搜索要修改的表，然后设计表，直接修改需要修改的字段...
Numeric Overflow，SQL问题？Java Code问题？
bisal的专栏
07-08 2万+
这两天出差，出发前开发同学提了一个问题，开会间隙，有了一些想法，java代码中和Oracle中均有可能出现此错误，记录于此。一段mybatis的代码，查询一张表的数据，SQL类似如下，接受起始时间和截止时间，作为检索条件。现象：1. 开发库运行这段代码，正常返回，测试库运行这段代码，无记录返回的情况正常，只要有记录返回，无论有几条，均会报错，Numeric Overflow，注意这儿报的是java
数值溢出（arithmetic overflow）问题与解决方案
09-03 1万+
1. 求最小公倍数 根据最大公约数求最小公倍数；lcm(a,b)=a⋅bgcd(a,b) \text{lcm}(a,b)=\frac{a\cdot b}{\text{gcd}(a,b)} int gcd(int a, int b); int lcm(int a, int b){ return (a * b)/gcd(a, b); }数学意义上确实没有异议，但在编程实现时，考虑到计算机表示
numwidth 对 to_number( ) 的约束
cobraqing7177的博客
02-05 418
SQL> SELECT to_number('1.99999999999999',9999999.99999999999999999999) from dual;TO_NUMBER('1.99999999999999',9...
ORACLE中常见SET指令
热门推荐
专注于ORACLE
07-15 4万+
Oracle中常见SET指令
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值