PostgreSQL – Best Way to Drop/Recreate Table with Dependent Views

postgresqlpostgresql-9.2

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.

Best Answer

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:

Related Solutions

PostgreSQL – How to Drop an Unnamed Check Constraint in a Table

The constraint has a name, whether you specified it or not.

From psql, \d foo will list all table constraints, along with their automatically assigned names.

You can also find these by querying the catalog tables directly:

SELECT conname
FROM pg_constraint
WHERE
  conrelid = 'foo'::regclass AND
  contype = 'c'

The table name can be schema-qualified if necessary (e.g. 'public.foo'::regclass).

contype = 'c' filters it down to CHECK constraints; the contype values for other constraint types are documented here.

PostgreSQL – DROP TABLE CASCADE Does Not Drop Dependent Tables

The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to:

CASCADE

Automatically drop objects that depend on the table (such as views).

(Emphasis mine.)

When you are dropping a table that is referenced by another table, the object that immediately depends on the table being dropped is not the other table itself but the foreign key constraint defined on it.

So, the behaviour you are observing should be expected as it is consistent with the other cases you have mentioned:

DROP TABLE ... CASCADE drops the views that directly depend on the table you are dropping.
DROP DOMAIN ... CASCADE drops the columns that directly depend on the domain you are dropping.
TRUNCATE ... CASCADE is also consistent with the explanation above because it removes rows, and the objects dependent on rows can only be other rows, including other tables' rows – that is why the referencing tables are truncated as well¹.

_{¹My only issue with the other tables' truncation is that their foreign keys may be defined on nullable columns and yet the tables are still truncated completely even if some of their rows do not reference the table(s) specified in the TRUNCATE statement. Still, even if it is an inconsistency, it is how the behaviour is documented:}

_CASCADE

_{Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.}