PostgreSQL 從熊燦燦一個獲取固定字元的SQL 分析巧妙之處

第一個位元組為 numeric (n,m) 的N， 最後一個位元組為 m+4，即precision為第一個位元組，scale為最後一個位元組-4

最後這裡順道將一個大家都在用的表欄位型別表格列印的語句貼上上，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;