时间戳

首页 > 解决方案 > 如何查找表中特定列和模式中所有表的数据字段的最大长度

postgresql-9.5 - 如何查找表中特定列和模式中所有表的数据字段的最大长度

问题描述

晚上好我们在数据库中有多个模式中的多个表。我们正在努力找出所有表和所有模式的表中每一列的最大大小。

列名示例：a,b,c,d,e,f,g 模式名示例：A,B,C,D

预期输出：column_name Max_size_of_column

或 column_name Max_size_of_column column_table table_schema

我尝试了以下查询，但无法获得所需的输出。

SELECT (select column_name from INFORMATION_SCHEMA.COLUMNS
where table_name='building'), 
select max(length(select column_name from INFORMATION_SCHEMA.COLUMNS 
where table_name='building')) from from INFORMATION_SCHEMA.COLUMNS 
where table_name='building'
group by column_name;

请帮助我们获得所需的输出。谢谢

标签： postgresql-9.5

解决方案

对于这样的问题，您需要某种动态 SQL。但是在 Postgres 中，这可以在不需要 PL/pgSQL 函数的情况下完成，类似于这个答案中的方法，但结合了 max() 和 length()：

with all_lengths as (
  select table_schema, table_name, column_name,
         query_to_xml(format('select max(length(%I)) from %I.%I', column_name, table_schema, table_name), false, true, '') as xml_max
  from information_schema.columns
  where table_schema in ('public') -- add the schemas you want here
    and data_type in ('text', 'character varying')
)
select table_schema, table_name, column_name, 
       (xpath('/row/max/text()', xml_max))[1]::text::int as max_length
from all_lengths
;

我没有可用的 Postgres 9.5，但我认为它也应该适用于旧版本。

如果您只想要特定的列，而不是所有具有 text 或 varchar 类型的列，那么只需将and data_type in (..)条件更改and column_name in (...)为 CTE 内部

推荐阅读