Add a Column to All of a Table’s Projections

Posted May 16, 2019 by Jim Knicely, Vertica Principal Solution Architect

Compass rose with true north pointing to "Helpful Tips" text
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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERTABLE.htm

Have fun!