Add Column to a Table a Position 1

[Julie]

Hey guys,

I know we can add columns to a table, but is there a way that we can add a new column to a table as the first column?

I mean if I add a column named new_column to a table I'd like for it to be listed first when described... I can do this in MySQL:

Code: Select all

(mydbadmin@localhost) [(none)]> desc intersect.test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  |     | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(mydbadmin@localhost) [(none)]> alter table intersect.test add new_col int first;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

(mydbadmin@localhost) [(none)]> desc intersect.test;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| new_col | int(11) | YES  |     | NULL    |       |
| col1    | int(11) | YES  |     | NULL    |       |
| col2    | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Is there an easy way to do this in Vertica?

[JimKnicely]

Julie,

There is no direct way of adding a column to a table at position one, but you can mimic the steps that MySQL performs behind the scene yourself.

For instance, I have a table named test:

Code: Select all

dbadmin=> select * from test;
 col1 | col2
------+------
    1 |    2
    3 |    4
(2 rows)

dbadmin=> \d test;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | test  | col1   | int  |    8 |         | f        | f           |
 public | test  | col2   | int  |    8 |         | f        | f           |
(2 rows)

I want to add a column called new_column having an int data type to the first position of the test table. First I have to create a temporary table with the added column:

Code: Select all

dbadmin=> create table test_tmp as select cast(null as int) new_column, col1, col2 from test;
CREATE TABLE
dbadmin=> \d test_tmp;
                                   List of Fields by Tables
 Schema |  Table   |   Column   | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------+------------+------+------+---------+----------+-------------+-------------
 public | test_tmp | new_column | int  |    8 |         | f        | f           |
 public | test_tmp | col1       | int  |    8 |         | f        | f           |
 public | test_tmp | col2       | int  |    8 |         | f        | f           |
(3 rows)

Now I can drop the original test table and rename my temporary table to test:

Code: Select all

dbadmin=> drop table test;
DROP TABLE
dbadmin=> alter table test_tmp rename to test;
ALTER TABLE
dbadmin=> select * from test;
 new_column | col1 | col2
------------+------+------
            |    1 |    2
            |    3 |    4
(2 rows)

dbadmin=> \d test;
                                  List of Fields by Tables
 Schema | Table |   Column   | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+------------+------+------+---------+----------+-------------+-------------
 public | test  | new_column | int  |    8 |         | f        | f           |
 public | test  | col1       | int  |    8 |         | f        | f           |
 public | test  | col2       | int  |    8 |         | f        | f           |
(3 rows)

[id10t]

Hi Julie.
In general - columns in RDBMS have no order. Why you need to add column in specific place?

[Julie]

sKwa,

Why you need to add column in specific place?

Because I deal with lunatics who like to see their brand new shiny column added to a particular spot so that when they view the table definition in their Toad client tool they'll know where to find it easier. This is especially true if they want to add a column that's kind of related to another column that already in the table. They want them next to each other! Icky, huh?

Thanks for your help!
Julie

PS. Sorry to get back to you on this topic so late!