How to add a new column to a table efficiently?

Best Answer

• Options

  marcothesane - Select Field - Administrator

  April 2020 Answer ✓

  Hi @yossibm
  The first documentation on the DEFAULT clause in a column definition can be found here:
  https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/column-constraint.htm
  Scroll to "DEFAULT" in the column constraint tabular list.
  So you need to derive the content of your new column from sources outside of the current table.
  Then you need to check this part - "Flattened Tables" in the Vertica documentation:
  https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm?zoom_highlight=flattened table
  Here, we introduce the concept that you can use also a single-row-single-column select as an expression for a column DEFAULT.
  Let me put together a Mickey Mouse example:

  -- this is the base table
  -- as yet without the column with the complex calculation
  CREATE TABLE mickey (
    id         BIGINT      NOT NULL
  , first_name VARCHAR(32)
  , last_name  VARCHAR(32)
  , hdate      DATE
  );
  -- out CREATE TABLE
  
  -- insert some data ...
  INSERT /*+ DIRECT */ INTO mickey(id,first_name,last_name,hdate)
            SELECT  1,'Arthur','Dent'           ,DATE '2010-04-01'
  UNION ALL SELECT  2,'Ford','Prefect'          ,DATE '2011-04-01'
  UNION ALL SELECT  3,'Zaphod','Beeblebrox'     ,DATE '2012-04-01'
  UNION ALL SELECT  4,'Tricia','McMillan'       ,DATE '2013-04-01'
  UNION ALL SELECT  5,'Gag','Halfrunt'          ,DATE '2014-04-01'
  UNION ALL SELECT  6,'Prostetnic Vogon','Jeltz',DATE '2015-04-01'
  UNION ALL SELECT  7,'Lionel','Prosser'        ,DATE '2016-04-01'
  UNION ALL SELECT  8,'Benji','Mouse'           ,DATE '2017-04-01'
  UNION ALL SELECT  9,'Frankie','Mouse'         ,DATE '2018-04-01'
  UNION ALL SELECT 10,'Svlad','Cjelli'          ,DATE '2019-04-01'
  ;
  -- out  OUTPUT 
  -- out --------
  -- out      10
  
  -- this is the helper table 
  -- to contain the complex-calculated value 
  -- needed finally in the "mickey" table
  CREATE TABLE helper (
    id            INT
  , months_worked INT
  );
  -- out CREATE TABLE
  
  -- this is to mimick the complex ETL calculation
  INSERT INTO helper
  SELECT
    id
  , MONTHS_BETWEEN(CURRENT_DATE,hdate)
  FROM mickey;
  -- out  OUTPUT 
  -- out --------
  -- out      10
  COMMIT; 
  -- out COMMIT
  
  -- check content of helper table
  SELECT * FROM helper;
  -- out  id | months_worked 
  -- out ----+---------------
  -- out   1 |           121
  -- out   2 |           109
  -- out   3 |            97
  -- out   4 |            85
  -- out   5 |            73
  -- out   6 |            61
  -- out   7 |            49
  -- out   8 |            37
  -- out   9 |            25
  -- out  10 |            13
  -- out (10 rows)
  -- out 
  
  -- Now - fix the "mickey" table:
  ALTER TABLE mickey
  ADD months_worked INTEGER DEFAULT(SELECT months_worked FROM helper WHERE id=mickey.id);
  -- out ALTER TABLE
  --check the contents of the "mickey" table now ...
  SELECT * FROM mickey;
  -- out Time: First fetch (0 rows): 120.181 ms. All rows formatted: 120.244 ms
  -- out  id |    first_name    | last_name  |   hdate    | months_worked 
  -- out ----+------------------+------------+------------+---------------
  -- out   1 | Arthur           | Dent       | 2010-04-01 |           121
  -- out   2 | Ford             | Prefect    | 2011-04-01 |           109
  -- out   3 | Zaphod           | Beeblebrox | 2012-04-01 |            97
  -- out   4 | Tricia           | McMillan   | 2013-04-01 |            85
  -- out   5 | Gag              | Halfrunt   | 2014-04-01 |            73
  -- out   6 | Prostetnic Vogon | Jeltz      | 2015-04-01 |            61
  -- out   7 | Lionel           | Prosser    | 2016-04-01 |            49
  -- out   8 | Benji            | Mouse      | 2017-04-01 |            37
  -- out   9 | Frankie          | Mouse      | 2018-04-01 |            25
  -- out  10 | Svlad            | Cjelli     | 2019-04-01 |            13
  -- out (10 rows)
  

  This should work, shouldn't it?

  5