Compare order of columns between two databases

Question

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

• user1822: The order of columns in a table is irrelevant (which is one of the reasons select * is bad coding style). Why do you need to verify this?
• András Váczi: @a_horse_with_no_name it is, usually, except the cases where the gaps produced by alignment are costly enough.
• guettli: @a_horse_with_no_name I have legacy code which does INSERT INTO phone_book VALUES ('John Doe', '555-1212');. Here the order of the columns is relevant.
• user1822: Which is even worse than select * - you have my sympathy.

Answer 1

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.

• guettli: order by .. ordinal_position that col was new to me. Thank you very much :-)
• guettli: Just for the records. Next step would be to call this on remote and local and compare the result. Since quoting quotes can be non trivial in the shell I search for a cleaner solution: softwarerecs.stackexchange.com/questions/26377/…
• user1822: @guettli: the easiest solution is to not use the -c switch, but to store the statement in a file and use -f foo.sql
• guettli: yes, this works for psql. But in my softwarerec question I search something which works for more then psql. I have very simple solution in my mind. But I wait. Maybe someone has a better idea :-)