Moving columns in Teradata

Question

I´ve created one new column in Teradata, and then I have moved the data from another colum into this new colmn. After that, I've deleted the old column. What I would like to do now is to move the new column n the same place in the table where the old column was. I would like to ask you - is that possible or not?

Answer 1

Unless the business requirement enforces the ordinal positioning of a column in a table definition (e.g. timestamp is the last column in the table definition), it doesn't really matter. If you have a V1 layer that maps views column for column (1:1) with proper ROW ACCESS locking, your view definition can always expose the preferred ordinal positioning of your columns to your end users and BI applications.

If you must re-order your columns perform the following:

1. Submit a CREATE TABLE MyDB.MyTable_ with your preferred ordinal position, column defaults, and any value list compression.
2. COLLECT STATISTICS ON MyDB.MyTable_ FROM MyDB.MyTable; -- Migrate collected statistics
3. INSERT INTO MyDB.MyTable_ SELECT /* Columns */ FROM MyDB.MyTable;
4. RENAME MyDB.MyTable AS MyDB.MyTable1; RENAME TABLE MyDB.MyTable_ AS MyDB.MyTable;
5. After validating everything is in order, DROP TABLE MyDB.MyTable1;

You could also do the following instead of Step 1 and 3:

CREATE TABLE MyDB.MyTable_ 
       (/* Column Definitions in new Order */ ) AS 
       (SELECT /* New Column Ordering */ 
          FROM MyDB.MyTable)
       WITH DATA AND STATISTICS
       {UNIQUE} PRIMARY INDEX (/* Primary Index Columns */);

Stats may or may not come over populated using this approach. You will want to verify this after the new table is created.

Answer 2

Since this is related to your previous question, I'll try to answer in the same context.

No, you cannot change the order of columns after the fact. When you "add" a new column to an existing table, it will be defined at the end of the table description. If you really need the columns to appear in a specific order in the physical table, you must recreate the table completely.

However, you can create a VIEW that displays the columns in whatever order you want. In fact, if you are going to use this table for some sort of application, you should create a VIEW in any case (so you can establish the proper access lock). For example, suppose you have a table named MYTABLE with three columns COL_A, COL_B, and COL_C and you want them "displayed" in reverse order:

replace view MYDB.MYTABLE_V as
locking MYDB.MYTABLE for access
select COL_C, COL_B, COL_A
from MYDB.MYTABLE

Using "replace view" will create the view if it does not already exist or replace it if it does. Of course, this means that if you ever alter the table, you must recreate the view again. I keep view definitons in a comment block in my table creation programs just to be safe.