如何修改查询使返回的表列按ordinal position顺序排列？

你需要把ORDER BY ordinal_position放到array_agg函数内部，这样聚合生成的列数组就会按照列的序号位置排序。修改后的完整查询如下：

SELECT t.table_name, array_agg(c.column_name::text ORDER BY c.ordinal_position) 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;

说明 #

• array_agg函数支持在括号内添加ORDER BY子句，用来指定聚合元素的排序规则，这是PostgreSQL的原生语法特性。
• 如果你把ORDER BY ordinal_position放到整个查询的末尾，只会对最终结果的表名进行排序，而不会改变每个表对应的列数组内部的顺序，这就是你之前尝试没得到预期效果的原因。

内容的提问来源于stack exchange，提问作者Nicki