0

Good Evening We have multiple tables in multiple schemas in DB. we are tyring to find out maximum size of each column in the table for all tables and for all schemas.

Example column names: a,b,c,d,e,f,g Example schema names: A,B,C,D

Expected output: column_name Max_size_of_column

or column_name Max_size_of_column column_table table_schema

I have tried the below query, but not able to get the desired output.

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;

Please help us to get the desired output. Thanks

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
postggen2020 s
  • 1

1 Answers1

0

You need some kind of dynamic SQL for questions like this. But in Postgres this can be done without the need of a PL/pgSQL function, similar to the approach from this answer but combining max() and 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
;

I don't have Postgres 9.5 available, but I think that should work with that old version as well.

If you just want specific columns, rather than all columns with a text or varchar type, then just change the and data_type in (..) condition to and column_name in (...) inside the CTE