Im running:
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.9.2 20150212 (Red Hat 4.9.2-6), 64-bit
I make use of table inheritance. I've created a table, added a
child table, and after that added a column to the parent table
(example code below).
As you can see below the newly added column is listed last in the
child table. I would have expected column 'c' to come after
column 'a', but can imagine that you can argue that it is safest
to put it last as people may have code that depends on column
positions.
However, when I dump the schema using pg_dump, and then load the
resulting sql file, suddenly 'c' does follow 'a'. So restoring my
schema has changed my table's layout. I feel dumping and loading
should not alter column positions. Any thoughts?
create schema test_issue;
create table test_issue.foo ( a integer );
create table test_issue.bar ( b text ) inherits ( test_issue.foo );
alter table test_issue.foo add column c integer;
postgres=# \d test_issue.bar;
Table "test_issue.bar"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | text |
c | integer |
Inherits: test_issue.foo
]$ ~/bin/pg_dump -n test_issue > test_issue.sql;
postgres=# drop schema test_issue cascade;
]$ psql -f test_issue.sql;
postgres=# \d test_issue.bar;
Table "test_issue.bar"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
c | integer |
b | text |
Inherits: test_issue.foo
Ingmar