Postgres find max column value from multiple tables

Question

I have list of tables that have specific column names like

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'column1'

I need to find the max value of column1 for each tables. I expect result like the following

|--------|--------------|
| Table  |  Max column1 |
|--------|--------------|
| Table1 |     100      |
| Table2 |     200      |
|  ...   |     ...      |
|--------|--------------|

How can I construct a query?

score 3 · Accepted Answer · answered Jan 08 '19 at 13:31

You can use a variation of the row count for all tables approach:

select t.table_name, 
       (xpath('/row/max/text()', xmax))[1]::text::int
from (
  SELECT table_name, data_type, 
         query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, '') as xmax
  FROM information_schema.columns
  WHERE column_name = 'column1'
    and table_schema = 'public'
) as t;

query_to_xml() runs a select max(..) from .. for each column returned from the query. The result of that is something like:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <max>42</max>
</row>

The xpath() function is then used to extract the value from the XML. The derived table (sub-query) is not really needed, but makes the xpath() expression more readable (in my opinion).

score 0 · Answer 2 · answered Jan 08 '19 at 13:48

You may create a generic function that returns a TABLE type by constructing a UNION ALL query from information_schema.columns

CREATE OR REPLACE FUNCTION public.get_max(TEXT )
RETURNS  TABLE(t_table_name  TEXT, t_max text )
LANGUAGE plpgsql
AS $BODY$
DECLARE
p_colname        TEXT := $1;
v_sql_statement TEXT;

BEGIN

SELECT STRING_AGG( 'SELECT '''||table_name||''','||' MAX('
       || column_name||'::text'
       || ')  FROM '
       || table_name 
         ,' UNION ALL ' ) INTO v_sql_statement
FROM   information_schema.columns 
WHERE  column_name = p_colname 
      --and table_schema = 'public';

    IF v_sql_statement IS NOT NULL THEN
       RETURN QUERY EXECUTE  v_sql_statement;
    END IF;
END
$BODY$;

Execute and get the results like this.

knayak=# select * FROM get_max('id');
 t_table_name | t_max
--------------+-------
 f            | 2
 t2           | 1
 friends      | id4
 person       | 2
 customer     |
 employe      |
 diary        | 4
 jsontable    | 6
 atable       |
 t_json       | 2
 ingredients  | 1
 test         | 2
 accts        |
 mytable      | 30
(14 rows)

Postgres find max column value from multiple tables

2 Answers2

Linked