Add column to table

[fsalvelt]

How can we add a column to tables? Is it kind of the same way as the delete column post?

[JimKnicely]

Hi,

Yes, to add a column to a table you have to follow the same basic steps as were discussed in the "How to drop columns from a table" post:

• viewtopic.php?f=7&t=4

When adding a column, you can follow these simple steps:

• 1) Create a new table having the same basic structure as the original table but with the addition of your new column(s).
  2) Copy the data from the original table to the new table
  3) Grant the same table privileges on the new table that were on the original table
  4) Drop the original table
  5) Rename the new table to the original table's name
  6) Analyze the the new table

Here is an example where I want to add the age column to the employee table:

Assume we begin with a table named employee which stores employee names.

Code: Select all

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

dbadmin=> select * from employee;
 emp_uid | emp_name
---------+----------
       1 | Beth
       2 | Samantha
       3 | Alex
(3 rows)

First, we'll create a new table named employee_temp that has the same columns as the employee table and the new emp_age column:

Code: Select all

dbadmin=> create table employee_temp (emp_uid int, emp_name varchar(100), emp_age int);
CREATE TABLE

dbadmin=> \d employee_temp;
                                         List of Fields by Tables
 Schema |     Table     |  Column  |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+---------------+----------+--------------+------+---------+----------+-------------+-------------
 public | employee_temp | emp_uid  | int          |    8 |         | f        | f           |
 public | employee_temp | emp_name | varchar(100) |  100 |         | f        | f           |
 public | employee_temp | emp_age  | int          |    8 |         | f        | f           |
(3 rows)

Next, we'll copy the data from employee table to employee_temp table:

Code: Select all

dbadmin=> insert into employee_temp select emp_uid, emp_name, null from employee order by emp_uid;
 OUTPUT
--------
      3
(1 row)

Next we'll copy the user privileges that exist on the employee table to the employee_temp table. First we have to identify the privileges that are on the employee table. For that we can query the GRANTS system table to create grant statement for the new table:

Code: Select all

dbadmin=> select 'grant ' || privileges_description || ' on employee_temp to ' || grantee || ';' grants_for_new_table from grants  where object_name = 'employee';
                             grants_for_new_table
-------------------------------------------------------------------------------
grant INSERT, SELECT, UPDATE, DELETE, REFERENCES on employee_temp to dbadmin;
grant SELECT on employee_temp to cognos;

dbadmin=>  grant SELECT on employee_temp to cognos;
GRANT PRIVILEGE

Next we'll drop the employee table:

Code: Select all

dbadmin=> drop table employee;
DROP TABLE

Next we'll rename the employee_temp table to the original table name (employee):

Code: Select all

dbadmin=> alter table employee_temp rename to employee;
ALTER TABLE

If you want, you can verify that the table grants are still intact for the employee table:

Code: Select all

dbadmin=> select grantee, privileges_description from grants  where object_name = 'employee';
 grantee |           privileges_description
---------+--------------------------------------------
 dbadmin | INSERT, SELECT, UPDATE, DELETE, REFERENCES
 cognos  | SELECT
(2 rows)

Finally, analyze the table so that Vertica can chose the appropriate SQL execution plans later:

Code: Select all

dbadmin=> select analyze_statistics('employee');
 analyze_statistics
--------------------
                  0
(1 row)

[fsalvelt]

Thanks. Why do you think that a DROP and ADD option isn't available out of the box?

[id10t]

Why do you think that a DROP and ADD option isn't available out of the box?

As I know Vertica team already works on it and... who knows may be it will be available in a next update or version.

[becky]

The simple things...

[rajasekhart]

Hi knicely,

I think , we have one more solution to add a column to a table.

simply, type the below syntax

Alter table <tablename> add <Column Name> <Datatype> <null>

Eg:

create table employee(id int, last_name varchar);

insert into employee
select 1, 'raj' union
select 2, 'Rahul' union
select 3, 'Sachin' union
select 4, 'Sehwag';

select * From employee;

id last_name
1 raj
2 Rahul
3 Sachin
4 Sehwag

Alter table employee add Department varchar;

Note: The column that was added must be a nullable one.
select * From employee;

id last_name Department
1 raj
2 Rahul
3 Sachin
4 Sehwag


Thank you,
Rajasekhar.T

[JimKnicely]

Well I'll be darned. Thanks, Rajasekhar! That's why I created this site, so that I can learn from others. Not sure how I missed this in the documentation...