Alternative solution to ALTER table ADD column


To add columns to an existing table, the current method involves deleting the table and recreating it, as ALTER table ADD column is not supported yet.

Here are a couple of approaches:

  1. Preserving Original Table Name:
    • Create a temporary table identical to the original one.
    • Insert data from the original table into the temporary table.
    • Delete the original table.
    • Recreate the original table with the new column(s).
    • Insert data from the temporary table, adding nulls for new columns.
  2. Not Preserving Original Table Name:
    • Create a new table (Table_V2).
    • Insert data from the original table into the new one.
    • Add nulls as new columns.

However, a more optimal solution will be to create a VIEW, which minimizes maintenance efforts compared to the above options and avoids extensive data ingestion, particularly for large tables.
Using this approach the steps would be:

  • Creating a view on top of the old table, adding null values (or any other constant) for the new columns and UNION the new table.
  • Direct queries to use from the VIEW instead of the original table.


This makes the process easier and more efficient, especially for large tables.
The steps to use this approach will be:

  • Old FACT table -
CREATE FACT TABLE IF NOT EXISTS old_tbl (col1 text not null,col2 int not null)
  • Create new FACT table -
CREATE FACT TABLE IF NOT EXISTS new_tbl (col1 text not null,col2 int not null,new_column text null)

Then create the view with this definition -

CREATE VIEW IF NOT EXISTS <view_name> AS SELECT col1 , col2 , <null/const value> from old_tblUNION ALLSELECT * from new_tbl