1

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 Aug 24 '18 at 19:45
  • @EvanCarroll I think that could work. – Brian Aug 27 '18 at 17:06
  • 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 Aug 27 '18 at 17:11
3

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? – a_horse_with_no_name Aug 24 '18 at 19:37
  • erg, I should have been cleaerer. Each additional text column will require a full table scan. – Evan Carroll Aug 24 '18 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. – a_horse_with_no_name Aug 24 '18 at 19:43
  • Yep, or use JSONB or something dba.stackexchange.com/a/215826/2639 – Evan Carroll Aug 24 '18 at 20:03
0

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}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.