I have added some column in the table, so can I alter the projection to include those column??
Please let me know how can I alter the projection.
How to alter the Projection.
Moderator: NorbertKrupa
Re: How to alter the Projection.
Hello Jess,
Welcome to Vertica Forms.
You cannot alter existing projections using any 'ALTER' keyword
All you can do is just alter the already existing projection's name using the 'ALTER' command
If you have added the new columns to your table and you want to make the changes accordingly into projection,
there are two options
1. Either create new projection with the all the columns existing in your table currently, you can do it manually using the CREATE PROJECTION statement
2. You runt the DBD and it wil do it for you.
The new projection will be having all your existing columns in the refered table.
Welcome to Vertica Forms.
You cannot alter existing projections using any 'ALTER' keyword
All you can do is just alter the already existing projection's name using the 'ALTER' command
If you have added the new columns to your table and you want to make the changes accordingly into projection,
there are two options
1. Either create new projection with the all the columns existing in your table currently, you can do it manually using the CREATE PROJECTION statement
2. You runt the DBD and it wil do it for you.
The new projection will be having all your existing columns in the refered table.
Re: How to alter the Projection.
Hello,
When I add a column to a table it is automatically added to the projections:
When I add a column to a table it is automatically added to the projections:
Code: Select all
dbadmin=> create table test (c1 int, c2 int, primary key (c1)) segmented by c1 all nodes;
CREATE TABLE
dbadmin=> select export_objects('', 'test');
export_objects
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE public.test
(
c1 int NOT NULL,
c2 int
);
ALTER TABLE public.test ADD CONSTRAINT C_PRIMARY PRIMARY KEY (c1);
CREATE PROJECTION public.test /*+createtype(P)*/
(
c1,
c2
)
AS
SELECT test.c1,
test.c2
FROM public.test
ORDER BY test.c1
SEGMENTED BY test.c1 ALL NODES KSAFE 1;
SELECT MARK_DESIGN_KSAFE(1);
(1 row)
dbadmin=> alter table test add column c3 int;
ALTER TABLE
dbadmin=> select export_objects('', 'test');
export_objects
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE public.test
(
c1 int NOT NULL,
c2 int,
c3 int
);
ALTER TABLE public.test ADD CONSTRAINT C_PRIMARY PRIMARY KEY (c1);
CREATE PROJECTION public.test /*+createtype(P)*/
(
c1,
c2,
c3
)
AS
SELECT test.c1,
test.c2,
test.c3
FROM public.test
ORDER BY test.c1
SEGMENTED BY test.c1 ALL NODES KSAFE 1;
SELECT MARK_DESIGN_KSAFE(1);
(1 row)
Thanks,
Juliette
Juliette
Re: How to alter the Projection.
Hello Julie,
I have gone through your sql, but unable to get it.
It seems you are manually creating projections after altering the table public.test
But isn't it Overwriting the previous projection and not altering it.
Please correct me, if I am wrong.
I have gone through your sql, but unable to get it.
It seems you are manually creating projections after altering the table public.test
But isn't it Overwriting the previous projection and not altering it.
Please correct me, if I am wrong.
Re: How to alter the Projection.
Hi nnani,
I am using Vertica 6.1.2. Maybe this is a new feature? I notice that only super projections are altered in this way. If I create my own projection, then add a column to a table, that column is not automatically added to the projection.
Here is an example:
Now I will add a column to the table TEST. It will automatically get added to the super projections, but not the projection I manually created...
What I don't like is the column c3 was place first in the sort order?!? I wonder why?
I am using Vertica 6.1.2. Maybe this is a new feature? I notice that only super projections are altered in this way. If I create my own projection, then add a column to a table, that column is not automatically added to the projection.
Here is an example:
Code: Select all
dbadmin=> create table test (c1 int, c2 int) segmented by c1 all nodes;
CREATE TABLE
dbadmin=> create projection test_julie as select c1 from test segmented by c1 all nodes;
CREATE PROJECTION
dbadmin=> select projection_name, is_super_projection from projections;
projection_name | is_super_projection
-----------------+---------------------
test_b0 | t
test_b1 | t
test_julie | f
(3 rows)
dbadmin=> select projection_name, projection_column_name
dbadmin-> from projection_columns
dbadmin-> order by projection_name, sort_position;
projection_name | projection_column_name
-----------------+------------------------
test_b0 | c1
test_b0 | c2
test_b1 | c1
test_b1 | c2
test_julie | c1
(5 rows)
Code: Select all
dbadmin=> alter table test add column c3 varchar(100);
ALTER TABLE
dbadmin=> select projection_name, projection_column_name
dbadmin-> from projection_columns
dbadmin-> order by projection_name, sort_position;
projection_name | projection_column_name
-----------------+------------------------
test_b0 | c3
test_b0 | c1
test_b0 | c2
test_b1 | c3
test_b1 | c1
test_b1 | c2
test_julie | c1
(7 rows)
Thanks,
Juliette
Juliette
Re: How to alter the Projection.
Thanks Julie,
I am working with Vertica 6.1.1, I tried to peep into the Release notes but nothing is mentioned in there
You can check the release notes for 6.1.2, might be you can get this update
Anyways, The new column added into the super projection automatically first in the sort order might be due to the timestamp the the columns were created in the projection. The new the timestamp the first may be the order.
Its just a guess
I am working with Vertica 6.1.1, I tried to peep into the Release notes but nothing is mentioned in there
You can check the release notes for 6.1.2, might be you can get this update
Anyways, The new column added into the super projection automatically first in the sort order might be due to the timestamp the the columns were created in the projection. The new the timestamp the first may be the order.
Its just a guess
Re: How to alter the Projection.
Julie - check the actual sort_position values. I suspect that the sort_position of the newly added column is actually NULL and gets printed first for that reason.
--Sharon
--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC