PostgreSQL How can i decode the NUMERIC precision and scale in pg_attribute.atttypmod
背景
早上给一个系统扩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$$;