PostgreSQL 从熊灿灿一个获取固定字符的SQL 分析巧妙之处

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群。

某天群里一个同学抛出一个问题，关于获取字段类型中的设置的值，随即熊老师在群里抛出以下的一个SQL  （秒抛）

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    
                   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            
        END
       ELSE null
  END AS numeric_scale,
  *
FROM
    pg_attribute
 where attrelid = 'xcc.xcc'::regclass and attname = 'number_b';

这个问题实际是对字段中的一种特殊的类型numberic 进行分析，并获取其中字段中的两个数字，并进行展示，听上去很简单。

首先我们从两个层面去分析这个SQL

1   结构层
这个SQL 主要展示的有两个层面  1  numeric_scale  2 numeric_scale
在基于 numeric_scale 的部分 7 个大判断，中包含1个子判断
numeric_scale 3个判断，包含1个子判断

2   判断逻辑
首先针对 atttypid 的值进行判断，这里需要说明的是 atttpid 的部分的值是需要搞清楚每个值代表的意义。

这里就产生一个问题了，atttpid 的值 与实际的字段的名字之间的对应关系，这里我个人通过各种方式并未找到 值与字段类型之间的对应关系表。

我个人能想到的方法就是建立一个包含大部分字段类型然后通过这个表来查到字段与atttypid 之间的关系。

所以写这个SQL 的人必然是做过这个基础性的工作，这实际上体现了撰写这个SQL 的人对于技术的严谨和认真。

判断逻辑中首先过滤了整形的数据类型部分，int2 int4 int8 等都会被显示，而本次提出问题的 numeric 中的字段进行判断在  atttypmod

这里对于实际的计算部分进行一个解释
numeric(5,4) => 327688   0101 0000 0000 0000 1000

numeric(5,5) => 327689   0101 0000 0000 0000 1001

numeric(2,2) => 393222   0110 0000 0000 0000 0110

numeric(7,2) => 458758   0111 0000 0000 0000 0110

numeric(8,2) => 524294   1000 0000 0000 0000 0110

numeric(9,2) => 589830   1001 0000 0000 0000 0110

第一个字节为 numeric (n,m) 的N， 最后一个字节为 m+4，即precision为第一个字节，scale为最后一个字节-4

计算公式：

atttypmod=-1表示null

precision: ((atttypmod - 4) >> 16) & 65535

scale: (atttypmod - 4) & 65535

所以通过上面的计算公式可以解决这个同学的问题。

另外为什么atttypmod 中为什么要减 4 ，这里我们我们通过一个实验可以获得，举例我们产生一个字段是varchar类型 ，这里给的值是200， 那么我们可以看下图atttypmod 是里面的值是204 ，那么从这里就可以了解到为什么上面的一个解释中要 m+4  ,而给出的SQL 中要进行一个 atttymod -4 的工作。

最后，通过这个SQL 实际上可以看出撰写者的对系统表的深刻理解，以及对于一些深层次系统表中 数字计算的部分，比如将  atttypmod -4 后变为16进制后与65535 进行位与运算。

实际上这一个SQL 可以研究的地方还不少，截止目前，我也觉得并未对一些更深的问题有更深的理解。

最后这里顺道将一个大家都在用的表字段类型表格打印的语句粘贴上，PG的系统表奥妙无穷。

select
c.relname as 表名,
a.attname as 列名,
(case
when a.attnotnull = true then true
else false end) as 非空,
(case
when (
select
count(pg_constraint.*)
from
pg_constraint
inner join pg_class on
pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on
pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = any(pg_constraint.conkey)
inner join pg_type on
pg_type.oid = pg_attribute.atttypid
where
pg_class.relname = c.relname
and pg_constraint.contype = 'p'
and pg_attribute.attname = a.attname) > 0 then true
else false end) as 主键,
concat_ws('', t.typname) as 字段类型,
(case
when a.attlen > 0 then a.attlen
when t.typname='bit' then a.atttypmod
else a.atttypmod - 4 end) as 长度,
col.is_identity as 自增,
col.column_default as 默认值,
(select description from pg_description where objoid = a.attrelid
and objsubid = a.attnum) as 备注
from
pg_class c,
pg_attribute a ,
pg_type t,
information_schema.columns as col
where
a.attnum>0
and a.attrelid = c.oid
and a.atttypid = t.oid
and col.table_name=c.relname and col.column_name=a.attname
order by
c.relname desc,
a.attnum asc;