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

Comments

Post a Comment

Popular posts from this blog

ruby on rails - Devise Logout Error in RoR -

ruby on rails - Devise Logout Error in RoR - while using devise in ror, encountering unusual error when trying log out url different root url. displays me : actioncontroller::invalidauthenticitytoken in devise::sessionscontroller#destroy" for illustration if rooting path after log in '/structure', , seek log out '/general' error, while logging out '/structure' fine. any help highly appreciated. ruby-on-rails ruby devise
Read more

c# - Create a Notification Object (Email or Page) At Run Time -- Dependency Injection or Factory -

c# - Create a Notification Object (Email or Page) At Run Time -- Dependency Injection or Factory - i have code emails me notification when happens. trying add together page , also remove dependency on email. for reference; have name of notification want send stored string in database. @ runtime have list of people , string representation of want receive. i thought dependency injection ninject, kernel bindings got big , seemed hacking together. then thought simple factory, activator.createinstance(email/page) inotifcation, don't know how handle differing constructor parameters (unless utilize common, complex property, employee, has info need). so far have this: public interface inotification { void sendmessage(); } public class email: inotification { private readonly string _toemailaddress; private string body { { homecoming "email"; } } private static string smtphost { ...
Read more

model view controller - MVC Rails Planning -

model view controller - MVC Rails Planning - i'm starting rails , i'm building app stores films info, user can watch film , give grade on web application. i have coded cinema model, controller , views. now, have 2 questions: should need create model , controller grading? in case, should create or should wait , create first users model , controller? yes, on model , controller grades. that'd feasible way maintain track of who's graded what. it's create first, i'd start users, since grades depend on both films , users. ruby-on-rails model-view-controller
Read more