Question

postgresql数据库中，查询一个字段允许的最大长度的SQL语句怎么写？

Answer 1

select
relname as 表名,
attname as 列名,
attnum as 列号,
case typname
when '_bpchar' then 'char'
when '_varchar' then 'varchar'
when '_date' then 'date'
when '_float8' then 'float8'
when '_int4' then 'integer'
when '_interval' then 'interval'
when '_numeric' then 'numeric'
when '_float4' then 'float4'
when '_int2' then 'smallint'
when '_text' then 'text'
when '_time' then 'time'
when '_timestamp' then 'timestamp'
end as 类型,
case typname
when '_bpchar' then atttypmod - 4
when '_varchar' then atttypmod - 4
when '_numeric' then (atttypmod - 4) / 65536
else attlen
end as 长度,
case typname
when '_numeric' then (atttypmod - 4) % 65536
else 0
end as 小数

from pg_stat_user_tables as a,
pg_attribute as b,
pg_type as c
where schemaname='u1'
and relname='t1'
and a.relid=b.attrelid
and b.attnum>0
and b.atttypid=c.typelem
and substr(typname,1,1)='_'
order by schemaname,relname,attnum;

where条件的地方 自己适当删减

Answer 2

对于character类型和character varying类型的字段，也可以用一下语句查询。
SELECT
attname as "name", typname as "type", atttypmod - 4 as "size"
FROM
pg_class AS a
LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
where a.relname = '<tablename>' and attname='<fieldname>'
and b.attstattarget = -1
order by attnum;
希望对你有所帮助。