The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Hi, Is It possible to add new column in table after checking whether column already exists or not?Ap

Hi, Is It possible to add new column in table after checking whether column already exists or not?Appreciate your help. Thank you.

Comments

  • It wasn't clear if you were trying to do this manually or programmatically. There's no function to test for exists, you'd have to use "\d tablename" or parse the results of "select * from tablename limit 0" to check for the column. In recent versions you can add a column to a table. See alter table in the sql guide for more details. Here's an example.

    => \d testaddcol
                                          List of Fields by Tables
     Schema |   Table    | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
    --------+------------+--------+-------------+------+---------+----------+-------------+-------------
     public | testaddcol | c1     | int         |    8 |         | f        | f           |
     public | testaddcol | c2     | varchar(20) |   20 |         | f        | f           |
    (2 rows)

    => select * from testaddcol;
     c1 | c2
    ----+-----
      2 | two
      1 | one
    (2 rows)

    => alter table testaddcol add column c3 timestamp default '2014-01-01';
    ALTER TABLE
    ndw=> \d testaddcol
                                                      List of Fields by Tables
     Schema |   Table    | Column |    Type     | Size |             Default              | Not Null | Primary Key | Foreign Key
    --------+------------+--------+-------------+------+----------------------------------+----------+-------------+-------------
     public | testaddcol | c1     | int         |    8 |                                  | f        | f           |
     public | testaddcol | c2     | varchar(20) |   20 |                                  | f        | f           |
     public | testaddcol | c3     | timestamp   |    8 | '2014-01-01 00:00:00'::timestamp | f        | f           |
    (3 rows)

    => select * from testaddcol;
     c1 | c2  |         c3
    ----+-----+---------------------
      2 | two | 2014-01-01 00:00:00
      1 | one | 2014-01-01 00:00:00
    (2 rows)

    I hope it helps.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.