Help to find-the-maximum-length-of-field-in-a-particular-column-in-all the tables

Please just choose a single list to email.

On Thursday, April 2, 2020, postgann2020 s <postgann2020@gmail.com> wrote:

Hi Team,

Good Evening,

We are trying to syncing the table field size with other db tables filed
size.

I wouldn’t bother fiddling with arbitrary sizes, just remove,them.

We have multiple tables in multiple schemas in DB. we are tyring to
findout maximum size of each column in table for all tables and for all
schemas in DB.

You need to decide whether you want to query table data or catalog (schema)
data.

I have tried below query, but not able to get 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;

If you want to dynamically determine tables and columns while still
checking the actual table data you will need to create a dynamic sql
statement to query the actual table with the content for the dynamic schema
parts being populated by a query against information_schema or the
catalog. You’d need you something besdies pure sql (e.g., pl/pgsql) to do
this. You may find it easier to just brute force things.

David J.

Thanks Joshuva and David.

Hi David,

We are trying to findout the max size of the data in columns for all tables
to resize the width of the assigned columns.

Regards,
Postgann.

On Fri, Apr 3, 2020 at 2:29 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Thu, Apr 2, 2020 at 10:15 PM postgann2020 s <postgann2020@gmail.com>
wrote:

We are trying to findout the max size of the data in columns for all
tables to resize the width of the assigned columns.

If you forgo brute force you will need to combine catalog access with
dynamic SQL. I'm not offering to work out the specific syntax for you. If
you've no better place to start the pl/pgsql documentation works as a
language that provides this capability. The main problem you are solving
is that identifiers (e.b., table and column names) in select queries must
be provided in the query text so you need to insert them (ideally using the
"format()" function and "%I (eye)" placeholder.

I feel it bears repeating that this exercise seems like a poor one to
perform (admittedly with zero actual knowledge as to the underlying
situation). Resizing them to "no size restriction" (i.e., "col_name text")
would be my preference. Higher risk fields might warrant constraints that
check content in addition to (or in lieu of) length. That fact that you
are wanting to perform this exercise in the first place would be sufficient
evidence that the previous decision to have field length limits was a poor
one. That my 0.02

David J.

Got it David.
Thanks I will try.

On Fri, Apr 3, 2020, 12:56 PM David G. Johnston <david.g.johnston@gmail.com>
wrote: