PostgreSQL How can i decode the NUMERIC precision and scale in pg_attribute.atttypmod

4 minute read

背景

早上给一个系统扩numeric长度, 扩字段SQL如下

alter table test alter column col type numeric(10,0);  

因为有众多子表, 在主表上操作完后准备检查一下所有的子表是否都自动扩展了长度.

digoal=> select attrelid::regclass,atttypmod from pg_attribute   
where attrelid in (select oid from pg_class where relname ~ '^test')   
and attname='col';  
          attrelid          | atttypmod   
----------------------------+-----------  
 test          |    655364  
 test_20120210 |    655364  

这里的atttypmod看起来和其他类型的不太一样, 定长类型的话这里直接就是字段字节数, 如果是变长类型, 那么是头+实际占用字节数.

http://blog.163.com/digoal@126/blog/static/163877040201351743331312/

对于numeric的精度计算和小数位计算, 通过atttypmod如何得到呢?

可以通过以下函数 :

information_schema._pg_numeric_precision  
information_schema._pg_numeric_scale  
postgres=# \df information_schema.*  
                                                    List of functions  
       Schema       |            Name             | Result data type |            Argument data types            |  Type    
--------------------+-----------------------------+------------------+-------------------------------------------+--------  
 information_schema | _pg_char_max_length         | integer          | typid oid, typmod integer                 | normal  
 information_schema | _pg_char_octet_length       | integer          | typid oid, typmod integer                 | normal  
 information_schema | _pg_datetime_precision      | integer          | typid oid, typmod integer                 | normal  
 information_schema | _pg_expandarray             | SETOF record     | anyarray, OUT x anyelement, OUT n integer | normal  
 information_schema | _pg_index_position          | integer          | oid, smallint                             | normal  
 information_schema | _pg_interval_type           | text             | typid oid, mod integer                    | normal  
 information_schema | _pg_keysequal               | boolean          | smallint[], smallint[]                    | normal  
 information_schema | _pg_numeric_precision       | integer          | typid oid, typmod integer                 | normal  
 information_schema | _pg_numeric_precision_radix | integer          | typid oid, typmod integer                 | normal  
 information_schema | _pg_numeric_scale           | integer          | typid oid, typmod integer                 | normal  
 information_schema | _pg_truetypid               | oid              | pg_attribute, pg_type                     | normal  
 information_schema | _pg_truetypmod              | integer          | pg_attribute, pg_type                     | normal  
(12 rows)  
digoal=> select information_schema._pg_numeric_scale(1700,655364);  
 _pg_numeric_scale   
-------------------  
                 0  
(1 row)  
digoal=> select information_schema._pg_numeric_precision(1700,655364);  
 _pg_numeric_precision   
-----------------------  
                    10  
(1 row)  

函数定义中用到的21, 23,20 ,1700等是数据类型的oid. pg_type.oid.

例如1700

digoal=> select typname from pg_type where oid=1700;  
 typname   
---------  
 numeric  
(1 row)  

具体的计算方法通过源码中numeric类型定义也能看出.

参见本文末尾.

也可以用这个函数

src/backend/utils/adt/format_type.c

/*  
 * SQL function: format_type(type_oid, typemod)  
 *  
 * `type_oid' is from pg_type.oid, `typemod' is from  
 * pg_attribute.atttypmod. This function will get the type name and  
 * format it and the modifier to canonical SQL format, if the type is  
 * a standard type. Otherwise you just get pg_type.typname back,  
 * double quoted if it contains funny characters or matches a keyword.  
 *  
 * If typemod is NULL then we are formatting a type name in a context where  
 * no typemod is available, eg a function argument or result type.  This  
 * yields a slightly different result from specifying typemod = -1 in some  
 * cases.  Given typemod = -1 we feel compelled to produce an output that  
 * the parser will interpret as having typemod -1, so that pg_dump will  
 * produce CREATE TABLE commands that recreate the original state.  But  
 * given NULL typemod, we assume that the parser's interpretation of  
 * typemod doesn't matter, and so we are willing to output a slightly  
 * "prettier" representation of the same type.  For example, type = bpchar  
 * and typemod = NULL gets you "character", whereas typemod = -1 gets you  
 * "bpchar" --- the former will be interpreted as character(1) by the  
 * parser, which does not yield typemod -1.  
 *  
 * XXX encoding a meaning in typemod = NULL is ugly; it'd have been  
 * cleaner to make two functions of one and two arguments respectively.  
 * Not worth changing it now, however.  
 */  
Datum  
format_type(PG_FUNCTION_ARGS)  
{  
        Oid                     type_oid;  
        int32           typemod;  
        char       *result;  
  
        /* Since this function is not strict, we must test for null args */  
        if (PG_ARGISNULL(0))  
                PG_RETURN_NULL();  
  
        type_oid = PG_GETARG_OID(0);  
  
        if (PG_ARGISNULL(1))  
                result = format_type_internal(type_oid, -1, false, true, false);  
        else  
        {  
                typemod = PG_GETARG_INT32(1);  
                result = format_type_internal(type_oid, typemod, true, true, false);  
        }  
  
        PG_RETURN_TEXT_P(cstring_to_text(result));  
}  

参考

1. http://blog.163.com/digoal@126/blog/static/1638770402013424113324797/

2. http://blog.163.com/digoal@126/blog/static/163877040201351743331312/

3. http://stackoverflow.com/questions/3350148/where-are-numeric-precision-and-scale-for-a-field-found-in-the-pg-catalog-tables

SELECT  
  CASE atttypid  
         WHEN 21 /*int2*/ THEN 16  
         WHEN 23 /*int4*/ THEN 32  
         WHEN 20 /*int8*/ THEN 64  
         WHEN 1700 /*numeric*/ THEN  
              CASE WHEN atttypmod = -1  
                   THEN null  
                   ELSE ((atttypmod - 4) >> 16) & 65535     -- calculate the precision  
                   END  
         WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/  
         WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/  
         ELSE null  
  END   AS numeric_precision,  
  CASE   
    WHEN atttypid IN (21, 23, 20) THEN 0  
    WHEN atttypid IN (1700) THEN              
        CASE   
            WHEN atttypmod = -1 THEN null         
            ELSE (atttypmod - 4) & 65535            -- calculate the scale    
        END  
       ELSE null  
  END AS numeric_scale,  
  *  
FROM   
    pg_attribute ;  

4. src/backend/utils/adt/numeric.c

/*  
 * numeric() -  
 *  
 *      This is a special function called by the Postgres database system  
 *      before a value is stored in a tuple's attribute. The precision and  
 *      scale of the attribute have to be applied on the value.  
 */  
        /*  
         * Get the precision and scale out of the typmod value  
         */  
        tmp_typmod = typmod - VARHDRSZ;  
        precision = (tmp_typmod >> 16) & 0xffff;  
        scale = tmp_typmod & 0xffff;  
        maxdigits = precision - scale;  

5. src/include/c.h

#define VARHDRSZ            ((int32) sizeof(int32))  

6. src/backend/catalog/information_schema.sql

information_schema._pg_numeric_precision  
information_schema._pg_numeric_scale  

精度

CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer  
    LANGUAGE sql  
    IMMUTABLE  
    RETURNS NULL ON NULL INPUT  
    AS  
$$SELECT  
  CASE $1  
         WHEN 21 /*int2*/ THEN 16  
         WHEN 23 /*int4*/ THEN 32  
         WHEN 20 /*int8*/ THEN 64  
         WHEN 1700 /*numeric*/ THEN  
              CASE WHEN $2 = -1  
                   THEN null  
                   ELSE (($2 - 4) >> 16) & 65535  
                   END  
         WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/  
         WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/  
         ELSE null  
  END$$;  

小数位

CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer  
    LANGUAGE sql  
    IMMUTABLE  
    RETURNS NULL ON NULL INPUT  
    AS  
$$SELECT  
  CASE WHEN $1 IN (21, 23, 20) THEN 0  
       WHEN $1 IN (1700) THEN  
            CASE WHEN $2 = -1  
                 THEN null  
                 ELSE ($2 - 4) & 65535  
                 END  
       ELSE null  
  END$$;  

Flag Counter

digoal’s 大量PostgreSQL文章入口