6 Replies Latest reply: Oct 15, 2012 12:25 PM by Arash RSS

    How to add new column in specific position

    scottjhn
      Say, a table has existing columns of A, B, D, and E.

      Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.

      How can I do it?
        • 1. Re: How to add new column in specific position
          AlbertoFaenza
          The only way is to drop and recreate he table.

          ALTER TABLE mytable ADD always add new columns at the end.

          Regards.
          Al
          • 2. Re: How to add new column in specific position
            Frank Kulash
            scottjhn wrote:
            Say, a table has existing columns of A, B, D, and E.

            Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.
            That's right; new columns are always added at the end.

            Why is is so important to have the columns in a specific order? If the order is important, why aren't you specifying it in the SQL code that actually uses the table?
            How can I do it?
            When you add C, add columns called NEW_D and NEW_E as well, in that order, where NEW_D has the same data type as D, and NEW_E has the same data type as E. Then UPDATE the table to set NEW_D and NEW_E to be the same as D and E, drop the original D and E, and rename NEW_D and NEW_E to the original names, D and E.
            • 3. Re: How to add new column in specific position
              Ashu_Neo
              What is problem if the column added at last ? In select clause you can mention any order of columns while using or retrieving data from table.

              If you really want check this , then rename existing to table to a different name and re-create the expected table by select columns (in order you want) from renamed table.
              So it will restore data and check for constraints only not null constraint can be copied back but for others, you have to create once again.

              Thanks!
              • 4. Re: How to add new column in specific position
                890672
                I appreciate your ways of thinking and the solution. But this has an performance issue. It will be a good solution for small data volume. For tables containing large data set it will take longer time.

                Even i believe that the order of table does not affect the query that run on them. I did not find any specific reason to put tables in order, as relational database is all about sets.
                Thanks
                • 5. Re: How to add new column in specific position
                  rp0428
                  >
                  Say, a table has existing columns of A, B, D, and E.

                  Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.

                  How can I do it?
                  >
                  You can't - you could create a view on the table that selects the columns in A, B, C, D, E order.
                  • 6. Re: How to add new column in specific position
                    Arash
                    create table emp (empno number primary key,ename varchar2(50),sal number);

                    insert into emp values(1,'smith',2000);
                    insert into emp values(2,'scott',3000);
                    commit;

                    /* add column hiredate as date type between empno and ename column in HR schema*/

                    create table emp2 as
                    select empno,sysdate hiredate,ename,sal
                    from emp
                    where 1=2;

                    EXEC Dbms_Redefinition.Start_Redef_Table('HR','emp','emp2','empno empno, ename ename, sal sal');
                    EXEC dbms_redefinition.sync_interim_table('HR','emp','emp2');
                    ALTER TABLE emp2 ADD (CONSTRAINT emp_pk2 PRIMARY KEY (empno));
                    EXEC Dbms_Redefinition.Finish_Redef_Table('HR','emp','emp2');
                    DROP TABLE emp2;

                    select * from emp;