Querying all tables and their column names in ordinal position

Question

I am able to fetch a table of names and their columns with the query below:

SELECT t.table_name, array_agg(c.column_name::text) as columns 
FROM information_schema.tables t inner 
JOIN information_schema.columns c on t.table_name = c.table_name 
WHERE t.table_schema = 'public' 
AND t.table_type= 'BASE TABLE' 
AND c.table_schema = 'public' group by t.table_name;

However, I want it to return the columns in order of ordinal position. How do I edit this query to do so?

I've tried adding ORDER BY ordinal_position but am unsure where to place it.

Answer 1

One way: add a per-aggregate ORDER BY:

SELECT t.table_name
     , array_agg(c.column_name::text ORDER BY c.ordinal_position) AS columns
FROM   information_schema.tables  t
JOIN   information_schema.columns c USING (table_name, table_schema)
WHERE  t.table_schema = 'public'
AND    t.table_type = 'BASE TABLE'
GROUP  BY t.table_name;

Alternatively, order rows in a subquery. See:

• How to sort objects in an array inside a json or jsonb value by a property of the objects?

While being at it, here's an alternative, basically equivalent query based on Postgres catalog tables:

SELECT relname AS table_name, array_agg (attname) AS columns
FROM  (
   SELECT c.relname, a.attname
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_attribute a ON a.attrelid = c.oid
   WHERE  c.relkind = 'r'
   AND    c.relnamespace = 'public'::regnamespace
   AND    a.attnum > 0
   AND    NOT a.attisdropped
   ORDER  BY 1, 2
   limit 10
   ) sub
GROUP  BY 1;

There are pros and cons. For details, see:

• information_schema vs. system catalogs