4

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.

Improve this question
8
  • 1
    There is no such possibility. What you could possibly do is to create the extended table anew in a separate schema, then all the dependent views, and finally swap in the new stuff and out the old. By the way, what's your reason for adding a new column in the middle of the table? There are cases when the column order is important, typically when the row number approaches the billion(s).
    – András Váczi
    Commented Mar 31, 2015 at 21:55
  • Column order is important. This is primarily to ease importing a somewhat large dataset from a web service. And to keep in line with the consistency of other tables, where there's a business practice to stick the record origination and modification fields in the last columns of the record. These fields have proved important to keep with the data instead of in a logging table. I could go into why these are important (and their location important), but suffice to say, they are.
    – vol7ron
    Commented Mar 31, 2015 at 22:03
  • 2
    I'd strongly suggest revising your design then. Relying on the order of columns is hardly ever a good practice. I admit that it is nicer to have consistency, but one has to keep an eye on the costs of maintaining the nicety. The only case when I really see an advantage in changing the column order is when you can win a few bytes per row with aligning the columns better - as said, in huge tables.
    – András Váczi
    Commented Mar 31, 2015 at 22:08
  • @dezso But you've hit the nail on the head there -- I'm looking for a way to do it and easily maintain it. Adding columns doesn't happen often, but being able to adjust logical order would save so much time. I've considered various options (wiki.postgresql.org/wiki/Alter_column_position) and it seems like 9.3 offers promising workaround with views, but I really am looking for something better. More than likely, I'll build my own script to make this easier. Generally, I'd agree that a new approach is optimal, but in this case I don't think I'm willing to budge.
    – vol7ron
    Commented Mar 31, 2015 at 22:15
  • I'd say the easiest way to do this is what I shortly described above. You could also attempt scripting the views programmatically, using the different catalogs and system information functions, dropping and recreating the table and then running the script with the views. BTW, what do you do with the existing data?
    – András Váczi
    Commented Mar 31, 2015 at 22:22

1 Answer 1

Reset to default
3

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:

Improve this answer

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.