Compare order of columns between two databases I  have two postgres databases which should have an equal db-schema, but have not.

I want to see the difference in the column order.

Columns of table1 in Dev: id, foo, bar

Columns of table1 in Prod: id, bar, foo

Strategy 1: list of columns

This is my current strategy to solve this. 

Dump all columns of the database like this:

table1.id
table1.foo
table1.bar
table2.id
table2....


If I have a simple ascii list of both sides, I can use a diff tool to compare both db schemas.

I found ways to output all columns like above but the solutions don't support ordering by the schema of the table.

Question

How to show the difference of the column order in two databases?

Other strategies are welcome.

Implementation: I use postgres 9.3
If I have a simple ascii list of both sides, I can use a diff tool to compare both db schemas.


To get a list of all table columns you can use something like this:

select concat_ws('.', table_schema, table_name, column_name) as column_name
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema, table_name, ordinal_position;


Of course you can limit that to the schema(s) you are interested in.

You can use psql's \copy statement to spool that to a file. Or use whatever export feature the SQL client you are using offers you.