Return to Revisions

I liked the "array" answer so much I wanted to add a complete example for PostgreSQL 9.3, using the generic information schema. This allows us to keep the subquery, and all of it's power. I'll definitely keep this in my back pocket.

Note that the array elements can't have mixed types, at least on 9.3.

SELECT
  table_data.table_schema,
  table_data.table_name,
  table_data.column_data[2] AS num_fields,
  table_data.column_data[3] AS min_column
FROM ( 
  SELECT table_schema,
         table_name,
         (SELECT ARRAY[c.table_name, COUNT(1)::text, MIN(c.column_name)]
            FROM information_schema.columns c
           WHERE c.table_name = t.table_name
             AND c.table_schema = t.table_schema
           GROUP BY c.table_name
         ) AS column_data
    FROM information_schema.tables t
   WHERE table_schema = 'information_schema'
     AND table_name LIKE 'column%'
) AS table_data;

    table_schema    |     table_name      | num_fields |        min_column
--------------------+---------------------+------------+--------------------------
 information_schema | column_domain_usage | 7          | column_name
 information_schema | column_privileges   | 8          | column_name
 information_schema | column_udt_usage    | 7          | column_name
 information_schema | columns             | 44         | character_maximum_length
 information_schema | column_options      | 6          | column_name