Can we add a column at a specific position to a table?

Question

In the context of this exercise, can we add a column at a specific position to a table?

Answer

No, unfortunately, you cannot specify what position to add a column to a table.

By default, a new column will always be added at the end of the table. For most intents and purposes, this should not affect much, since you can always select the columns in any order, for instance, like

SELECT col3, col1, col2

If column order is very important, then an alternative is to create a new table and add the columns in the specific order they should appear.

25 Likes

Ok, that was useful!

How would the order/index of columns affect anything?

To me it seems like something better left for the implementation. From the user (don’t want to hard-code index) and theoretical (sets and relations) standpoint this does not seem meaningful.

Besides a view seem like a much better idea than a whole new table (the cost of copy?!)

I more detailed answer would be appreciated, perhaps clarifying

  • how is the data stored?
  • how does ALTER work and what is the cost?
  • link/example/reference to future lesson about making new table/view and the cost of that