ALTER table in vertica

Question

I have table in Vertica which has time_stamp:int with value Unix timestamp, I want to alter this data type from int to TIMESTAMPTZ;

I tried

ALTER TABLE exploded_names ALTER COLUMN time_stamp SET DATA TYPE TIMESTAMPTZ;

But got following error

ROLLBACK 2353:  Cannot alter type of column "time_stamp" since it is referenced in the segmentation expression of projection "exploded_names_b0"

then I tried to drop that projection :

drop projection exploded_names_b0 CASCADE;

ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection exploded_names_b0
HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead

I don't want to drop the table.

Answer 1

The error you received is telling you that the column is used in the segmentation expression for the projection and thus can't be changed. This is a restriction for the SET DATA TYPE option. You can either create new superprojections and omit the column in the segmentation clause, or create a new table and projections with the new column type.

Since you mentioned that your current column is an int type, it won't convert to TIMESTAMPTZ. Here's what I would do:

Step 1 - Set up sample data

CREATE TABLE public.exploded_names (
   id int,
   time_stamp int
);

INSERT INTO public.exploded_names (id, time_stamp) VALUES (1, 1403635837);

COMMIT;

Step 2 - Add new column

ALTER TABLE public.exploded_names ADD COLUMN iso_time TIMESTAMPTZ;

Step 3 - Create new super projection

We'll get the existing super projection using SELECT EXPORT_OBJECTS('', 'public.exploded_names');

CREATE PROJECTION public.exploded_names /*+createtype(L)*/
(
 id,
 time_stamp,
 iso_time
)
AS
 SELECT exploded_names.id,
        exploded_names.time_stamp,
        exploded_names.iso_time
 FROM public.exploded_names
 ORDER BY exploded_names.id,
          exploded_names.time_stamp
SEGMENTED BY hash(exploded_names.id, exploded_names.time_stamp) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

We'll need to remove the time_stamp column and add iso_time on the segmentation clause and change the name for the new superprojection:

CREATE PROJECTION public.exploded_names_2
(
 id,
 time_stamp,
 iso_time
)
AS
 SELECT exploded_names.id,
        exploded_names.time_stamp,
        exploded_names.iso_time
 FROM public.exploded_names
 ORDER BY exploded_names.id,
          exploded_names.time_stamp
SEGMENTED BY hash(exploded_names.id, exploded_names.iso_time) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

Step 4 - Populate the new column

Here we'll update the iso_time column with a converted Unix timestamp and then refresh the new superprojection.

UPDATE public.exploded_names SET iso_time = TO_TIMESTAMP(time_stamp);

COMMIT;

SELECT REFRESH('public.exploded_names');

Step 5 - Drop old superprojection

We'll need to advance the ancient history marker and then drop the old superprojection:

SELECT MAKE_AHM_NOW();

DROP PROJECTION public.exploded_names;

Step 6 - Verify projections

Let's make sure we have the correct projection set up SELECT GET_PROJECTIONS('public.exploded_names');:

 Current system K is 1.
# of Nodes: 3.
Table public.exploded_names has 2 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
public.exploded_names_2_b1 [Segmented: Yes] [Seg Cols: "public.exploded_names.id", "public.exploded_names.iso_time"] [K: 1] [public.exploded_names_2_b0] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.exploded_names_2_b0 [Segmented: Yes] [Seg Cols: "public.exploded_names.id", "public.exploded_names.iso_time"] [K: 1] [public.exploded_names_2_b1] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

On a side note, if you haven't already, you should run Database Designer to get optimized projections.