
Example:
By default when you add a column to a table, Vertica only adds the column to the underlying super-projections.
dbadmin=> CREATE TABLE test (c1 INT, c2 INT);
CREATE TABLE
dbadmin=> CREATE PROJECTION test_pr1 AS SELECT * FROM test;
CREATE PROJECTION
dbadmin=> CREATE PROJECTION test_pr2 AS SELECT c1 FROM test;
CREATE PROJECTION
dbadmin=> ALTER TABLE test ADD COLUMN c3 INT;
ALTER TABLE
dbadmin=> SELECT projection_name, LISTAGG(projection_column_name) columns
dbadmin-> FROM projection_columns
dbadmin-> WHERE table_name = 'test'
dbadmin-> GROUP BY projection_name;
projection_name | columns
-----------------+----------
test_pr1_b0 | c1,c2,c3
test_pr2_b1 | c1
test_pr1_b1 | c1,c2,c3
test_pr2_b0 | c1
(4 rows)
I want to add my new column to all of the projections! This time, I’ll specify the ALL PROJECTIONS option on the ALTER TABLE command.
dbadmin=> drop table test cascade;
DROP TABLE
dbadmin=> CREATE TABLE test (c1 INT, c2 INT);
CREATE TABLE
dbadmin=> CREATE PROJECTION test_pr1 AS SELECT * FROM test;
CREATE PROJECTION
dbadmin=> CREATE PROJECTION test_pr2 AS SELECT c1 FROM test;
CREATE PROJECTION
dbadmin=> ALTER TABLE test ADD COLUMN c3 INT ALL PROJECTIONS;
ALTER TABLE
dbadmin=> SELECT projection_name, LISTAGG(projection_column_name) columns
dbadmin-> FROM projection_columns
dbadmin-> WHERE table_name = 'test'
dbadmin-> GROUP BY projection_name;
projection_name | columns
-----------------+----------
test_pr1_b0 | c1,c2,c3
test_pr2_b1 | c1,c3
test_pr1_b1 | c1,c2,c3
test_pr2_b0 | c1,c3
(4 rows)
Helpful Links:https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERTABLE.htm
Have fun!