Best way to drop / recreate a table that has dependent views? Problem



PostgreSQL does not have logical column reordering, at least not in 9.2

If columns need to be added in the middle of a large table and order is important, the best way is to drop and recreate the table.  However, if there are external dependencies, such as views, this is not possible without DROP CASCADE.

If there are lots of views built on this table, it seems like a lot of work.  Is there any way to accomplish this without having to drop/recreate the views?

Hopeful



My hope is that you can do something like DROP CASCADE DEFERRED in a transaction.  So that the dependencies only drop if the original table doesn't exist at the end of the commit, but giving you the ability to drop the table, create the table, and commit without losing changes.
If you have the luxory of exclusive access a very simple method is to draw a backup with pg_dump, reorder columns as you chose in the CREATE TABLE scripts of the dump and restore the database. Other parts of the dump do not depend on column order. For instance, COPY statements list columns explicitly.

If you don't have this luxory, the whole operation is a pain. You need an exclusive lock on the table while dropping and recreating it - and all depending objects like views in its tail.
And concurrent queries on the table will still produce exceptions because they wait for the lock to go away, but then the table is gone (the table name has been resolved to a particular OID early in the process).

The Postgres Wiki on "Alter column position" has some more advice.

Decoupling logical column ordering from physical column ordering has been discussed repeatedly on pgsql-hackers, there is an entry in the ToDo Wiki by the name:

Allow column display reordering by recording a display, storage, and permanent id for every column?

And efforts are being made to maybe get this into Postgres 9.6: