sql - ALTER table in vertica -

sql - ALTER table in vertica -

i have table in vertica has time_stamp:int value unix timestamp, want alter info type int timestamptz;

i tried

alter table exploded_names alter column time_stamp set info type timestamptz;

but got next error

rollback 2353: cannot alter type of column "time_stamp" since referenced in segmentation look of projection "exploded_names_b0"

then tried drop projection :

drop projection exploded_names_b0 cascade; rollback 4122: no up-to-date super projection left on anchor table of projection exploded_names_b0 hint: utilize drop table ... cascade drop anchor table , lastly projection, or create replacement super projection instead

i don't want drop table.

the error received telling column used in segmentation look projection , can't changed. restriction set info type option. can either create new superprojections , omit column in segmentation clause, or create new table , projections new column type.

since mentioned current column int type, won't convert timestamptz. here's do:

step 1 - set sample data

create table public.exploded_names ( id int, time_stamp int ); insert public.exploded_names (id, time_stamp) values (1, 1403635837); commit;

step 2 - add together new column

alter table public.exploded_names add together column iso_time timestamptz;

step 3 - create new super projection

we'll existing super projection using select export_objects('', 'public.exploded_names');

create projection public.exploded_names /*+createtype(l)*/ ( id, time_stamp, iso_time ) select exploded_names.id, exploded_names.time_stamp, exploded_names.iso_time public.exploded_names order exploded_names.id, exploded_names.time_stamp segmented hash(exploded_names.id, exploded_names.time_stamp) nodes ksafe 1; select mark_design_ksafe(1);

we'll need remove time_stamp column , add together iso_time on segmentation clause , alter name new superprojection:

create projection public.exploded_names_2 ( id, time_stamp, iso_time ) select exploded_names.id, exploded_names.time_stamp, exploded_names.iso_time public.exploded_names order exploded_names.id, exploded_names.time_stamp segmented hash(exploded_names.id, exploded_names.iso_time) nodes ksafe 1; select mark_design_ksafe(1);

step 4 - populate new column

here we'll update iso_time column converted unix timestamp , refresh 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 advance ancient history marker , drop old superprojection:

select make_ahm_now(); drop projection public.exploded_names;

step 6 - verify projections

let's create sure have right projection set select get_projections('public.exploded_names');:

current scheme k 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 side note, if haven't already, should run database designer optimized projections.

sql database vertica