How to get max field value length for each TEXT field in all tables in database

Question

I am trying to get information about the data in my database. I have several columns in several tables that or type 'TEXT'. I want to perform a query that will create a result that looks like:

|table_name|column_name|max_length|
-----------------------------------
|my_table  |name       |35        |

I have the query to get all the tables and columns:

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'text'
ORDER BY table_name

But I can't figure out how to now go through each of those tables and columns to get the max length of each column. I do have the query to get the max length of a specific column and table using:

SELECT 'my_table', 'name', MAX(LENGTH(name)) FROM my_table

How can I make a query that will get the max length dynamically based on the results of the query to get all tables and columns?

Do you care if the result is table_name|column_name|jsonb, where jsonb is [{column_name: max_length}] — Evan Carroll
– Evan Carroll, Commented Aug 24, 2018 at 19:45
As this was a one off where I just needed some information about my data, I decided to go the manual but simple approach. I took the results from my query for all the tables and columns and used a text editor to create a SELECT statement to get the max length for each column and UNIONed them all together. — Brian
– Brian, Commented Aug 27, 2018 at 17:11

score 8 · Accepted Answer · 2018-08-24 20:50:53Z

No need for PL/pgSQL.

You can use a variation of this solution to do everything in a single query.

select table_schema, 
       table_name,
       column_name, 
       (xpath('/row/max/text()',query_to_xml(format('select max(length(%I)) from %I.%I', column_name, table_schema, table_name), true, true, '')))[1]::text::int as max_length
from information_schema.columns
where table_schema = 'public'       
  and data_type = 'text'
order by table_schema, table_name, column_name;

Evan's comment got me thinking if there is a way to do this with a single statement per table (although I doubt that this performance optimization is really important for a "get me some information about my database query")

With a bit of XML juggling, it is indeed possible:

with counts as (
  select table_schema, 
         table_name,
         query_to_xml(format('select %s from %I.%I', string_agg(format('max(length(%I)) as %I', column_name, column_name), ', '), table_schema, table_name), 
                      true, true, '') as result
  from information_schema.columns
  where table_schema = 'public'       
    and data_type = 'text'
  group by table_schema, table_name
)
select table_schema, 
       table_name, 
       substring(x::text, 2, strpos(x::text,'>') - 2) as column_name,
       (xpath('./text()', x))[1]::text::int as max_length
from counts, unnest(xpath('//row/*', result)) as c(x);

For the pagila demo database this returns:

table_schema | table_name                 | column_name | max_length
-------------+----------------------------+-------------+-----------
public       | actor_info                 | film_info   |        830
public       | customer_list              | notes       |          6
public       | customer_list              | name        |         21
public       | film                       | description |        130
public       | film_list                  | actors      |        216
public       | film_list                  | description |        130
public       | nicer_but_slower_film_list | actors      |        201
public       | nicer_but_slower_film_list | description |        130
public       | sales_by_store             | manager     |         12
public       | sales_by_store             | store       |         19
public       | staff_list                 | name        |         12

The above first generates one statement per table containing one max(length(..)) for each column.

The result is then parsed and turned into rows again. It would be possible to aggregate that back into a single JSON.

To get one row per table, with the column information as a JSON value, the following can be used:

with counts as (
  select table_schema, 
         table_name,
         query_to_xml(format('select to_jsonb(t) as cols from (select %s from %I.%I) t', string_agg(format('max(length(%I)) as %I', column_name, column_name), ', '), table_schema, table_name), 
                      false, true, '') as result
  from information_schema.columns
  where table_schema = 'public'       
    and data_type = 'text'
  group by table_schema, table_name
)
select table_schema, 
       table_name, 
       (xpath('//row/cols/text()', result))[1]::text::jsonb as column_sizes
from counts;

For the pagila demo database this returns:

table_schema | table_name                 | column_sizes                       
-------------+----------------------------+------------------------------------
public       | actor_info                 | {"film_info": 830}                 
public       | customer_list              | {"name": 21, "notes": 6}           
public       | film                       | {"description": 130}               
public       | film_list                  | {"actors": 216, "description": 130}
public       | nicer_but_slower_film_list | {"actors": 201, "description": 130}
public       | sales_by_store             | {"store": 19, "manager": 12}       
public       | staff_list                 | {"name": 12}

@EvanCarroll: of course it will take into account multiple columns of the same table. Why do you think it won't? — user1822
– user1822, Commented Aug 24, 2018 at 19:37
erg, I should have been cleaerer. Each additional text column will require a full table scan. — Evan Carroll
– Evan Carroll, Commented Aug 24, 2018 at 19:39
@EvanCarroll: yes, because that's the only way to do it. Otherwise you would wind up with a result that has a different number of columns per row which isn't possible. — user1822
– user1822, Commented Aug 24, 2018 at 19:43
Yep, or use JSONB or something dba.stackexchange.com/a/215826/2639 — Evan Carroll
– Evan Carroll, Commented Aug 24, 2018 at 20:03

Evan Carroll · Accepted Answer · 2018-08-24 20:08:54Z

This will return a series of select statements that will only query each table once returning the maximum length for every text column,

SELECT FORMAT(
        'SELECT %L AS table_catalog, %L AS table_schema, %L AS table_name, %s FROM %I.%I.%I;',
        table_catalog,
        table_schema,
        table_name,
        string_agg(
                FORMAT('max(length(%I)) AS %I', column_name, column_name),
                ', '
                ORDER BY column_name
        ),
        table_catalog,
        table_schema,
        table_name
)
FROM information_schema.columns
WHERE data_type = 'text'
GROUP BY table_catalog, table_schema, table_name
ORDER BY table_catalog, table_schema, table_name;

Because the table can have different amount of text-columns, your only method of moving forward from that point is to use JSONB or some other schemaless type.

SELECT FORMAT(                                                                                                                                                           'SELECT %L AS table_catalog, %L AS table_schema, %L AS table_name, %s FROM %I.%I.%I;',
        table_catalog,
        table_schema,
        table_name,
        string_agg(
                FORMAT( 'jsonb_build_object(%L,max(length(%I)))', column_name, column_name),
                ' || '
                ORDER BY column_name
        ),
        table_catalog,
        table_schema,
        table_name
)
FROM information_schema.columns
WHERE data_type = 'text'
GROUP BY table_catalog, table_schema, table_name
ORDER BY table_catalog, table_schema, table_name;

From this point you can just UNION them all together.

SELECT FORMAT( '%s;', string_agg(stmt, E'\nUNION ALL ') )
FROM (
  SELECT FORMAT(
    'SELECT %L AS table_catalog, %L AS table_schema, %L AS table_name, %s FROM %I.%I.%I',
    table_catalog,
    table_schema,
    table_name,
    string_agg(
      FORMAT( 'jsonb_build_object(%L,max(length(%I)))', column_name, column_name),
      ' || '
      ORDER BY column_name
    ),
    table_catalog,
    table_schema,
    table_name
  )
  FROM information_schema.columns
  WHERE data_type = 'text'
    AND table_schema <> 'pg_catalog'
  GROUP BY table_catalog, table_schema, table_name
) AS t(stmt);

Running the result of that query (type \gexec if using psql) and your output should be something like,

 test          | master       | entities                  | {"entity_name": 6}
 test          | public       | geography_columns         | {"type": null}
 test          | public       | ip2loc_asn                | {"name": 29}
 test          | public       | t                         | {"event": 3}
 test          | public       | txns_combined             | {"item": 6, "to_party": 5, "from_party": 4}
 test          | tec          | codes_office              | {"office_id": 22, "office_name": 44}
 test          | tec          | codes_reports             | {"report_id": 15, "report_name": 70}
 test          | tec          | codes_texas_counties      | {"county_id": 3, "county_name": 13}
 test          | tec          | codes_total               | {"total_id": 22, "total_description": 112}
 test          | tec          | expendcategory            | {"recordtype": 5, "expendcategorycodelabel": 74, "expendcategorycodevalue": 10}

Stack Exchange Network

How to get max field value length for each TEXT field in all tables in database

2 Answers 2

Your Answer

Linked

Hot Network Questions

How to get max field value length for each TEXT field in all tables in database

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions