I'm trying to optimize the structure of a table. To do so, I've put the columns in the most optimal order possible and then ran this query (simplified):

INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;

Then I simply rename the table and add all the same indexes and foreign keys.

ALTER TABLE "table_current" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table_current";

This has worked great in the past, but in this particular case, there are a LOT of other tables with cascading foreign keys on this table.

So if I recreate and rename, then all the other tables still reference table_old in their foreign keys instead of the new table_current.

How do I do such a table migration like this without having to recreate the foreign keys on every other table as well?

  • There's nothing in pg_dump to help with this really. You'd need to write a query against pg_depend to find all the dependent objects then list them in a pg_dump table-list. Commented Jan 26, 2017 at 3:47

1 Answer 1


There is no easy way to work around the problem. When you drop columns that other things are referencing, like views or foreign keys, you must manually inform all areas of the schema that have dependencies on that column.

There is no shortcut that I know of around this. No one really makes a habit of reordering tables. Most of the requests are for cosmetic reasons, however if you're really doing it for a valid reason (like table packing) then I wish you the best of luck.

It's been a feature request for a while, and it comes up frequently.

  • Yeah, I've seen the requests. No one has taken up the challenge yet, unfortunately. :(
    – eComEvo
    Commented Jan 26, 2017 at 23:17
  • That's because the ideal solution is for physical and logical table ordering to be distinct and for physical ordering to be done internally based on packing. That's a huge job.. Commented Jan 26, 2017 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.