The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Add a Column to All of a Table’s Projections

Starting in Vertica 9.2.1, when you add a column to a table you can now tell Vertica to add that column to all of the table’s underlying projections. To do that, use the ALL PROJECTIONS clause of the ALTER TABLE … ADD COLUMN command.
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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERTABLE.htm
Have fun!
0