Add a Column to All of a Table’s Projections
Jim_Knicely
- Select Field - Administrator
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