ADD COLUMN AT SPECIFIC OFFSET

Pietro_La_Torre

Hi,
I want to add a column to a table in a specific position.
For example:

table : my_table (
a int,
c varchar,
d timestamp
);

I want to add column b between a and c. Is it possible?
thanks,
Pietro.

colin_loghin

I don't think this is possible because the sql command does not allow new column position specification. Internally, since a projection sort order can be affected by adding a column high in the projection sort order, it can trigger a rebuild of the entire table.

However, you could build a new table with full flexibility ( and column order desired) and swap the 2 tables to achieve the desired  outcome.

Daniel_Leybovic

I agree with Colin.
If it too much painful to recreate a table(projections), as work around you can use in VIEWs. Define right column order in view, while to base table column will be added to end of column's list.

BTW: "add column" (no matter a position) will require to refresh table projections, so recreating a table or adding column + creating VIEW will cost same time "price".