How to Add Table Column at Specific Position

In a perfect world, the database design would be firm and final before development starts. But we do not live in a perfect world – changes can and do happen. You forgot a column in a table, or your client has a change request – for various reasons, you may find that you need to add a column to an existing table.

Oracle allows you to alter a table easily for adding a column, but what if you want the new column added at a specific position in the table? Now you’re in a spot. There is no command to “alter table add column at position 2”; Oracle simply places the column after all the existing columns.

Technically speaking, the column order is unimportant. A relational database is about sets, and in sets the order of attributes and tuples does not matter. Whether the ID column is in position #1, #3 or #28 makes no difference to the way you would write queries on the table. If you can live with the column in the end, do, but if you have been blessed/cursed with a Poirotesque sense of symmetry, the haphazard arrangement of columns when you look into the data dictionary is going to bother you. The column order in a large table might also have a small impact on performance/storage.

Is there a way out? Yes there is.

Let’s see how to add a new column at a specific position in an existing table, using a simple example.

We have a table PERSON with this structure:

SQL> desc person
 Name              Null?    Type
 ----------------- -------- ------------
 ID                         NUMBER(3)
 NAME                       VARCHAR2(15)
 SAL                        NUMBER(6)

A new column ADDRESS is to be added to PERSON. The default ALTER TABLE operation to add column will produce this result:

SQL> alter table person
  2  add (address varchar2(40));

Table altered.

SQL> desc person
 Name              Null?    Type
 ----------------- -------- ------------
 ID                         NUMBER(3)
 NAME                       VARCHAR2(15)
 SAL                        NUMBER(6)
 ADDRESS                    VARCHAR2(40)

The requirement:

To move the new column ADDRESS after NAME and before SAL i.e. at position #3, shifting SAL to position #4.

The solution:

1. Stop modifications on the table till the operation is complete.
2. Rename the existing PERSON table.
3. Create a new table PERSON using CTAS on the renamed table, with the structure we want – i.e. ADDRESS at position #3, SAL at position #4.
4. Recreate the indexes, grants, constraints to the new PERSON table.
5. Verify that PERSON table now looks exactly as required, and drop the renamed old table.

SQL> rename person
  2  to person_old;

Table renamed.

SQL> create table person
  2  as
  3  select id   as id
  4       , name as name
  5       , address as address
  6       , sal  as sal
  7  from person_old;

Table created.

SQL> desc person
 Name              Null?    Type
 ----------------- -------- ------------
 ID                         NUMBER(3)
 NAME                       VARCHAR2(15)
 ADDRESS                    VARCHAR2(40)
 SAL                        NUMBER(6)

SQL> drop table person_old;

Notes:

This approach works well for tables with small data volume or those that can take some downtime. If you must have the table up and running while the new column is added, consider using the DBMS_REDEFINITION package.

Summary

In Oracle, the standard command to add a new column appends the new column to the very end of the table. This has no impact on the way the table gets queried, but if you still want to add table column at a specific position, the approach demonstrated above will do it for you.

If the table volume is large this is going to be a big operation. Be prepared for some downtime, or use DBMS_REDEFINITION.