Add a Column to All of a Table’s Projections

Jim_Knicely

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!