[Ilugc] altering tables with postgres

[Kenneth Gonsalves]

hi,

I have the following code which is self explanatory:

begin;
alter table web_stockist add column "contactperson" varchar(200);
alter table web_stockist add column "designation" varchar(200);

update web_stockist set contactperson = 'xyz';
update web_stockist set designation = 'xyz';

alter table web_stockist alter column contactperson set not null;
alter table web_stockist alter column designation set not null;
end;

it does not work. it complains: 'no column called contact person' and
later 'cannot set not null because there are pending transactions'. This
works:

begin;
alter table web_stockist add column "contactperson" varchar(200);
alter table web_stockist add column "designation" varchar(200);
end;
begin;
update web_stockist set contactperson = 'xyz';
update web_stockist set designation = 'xyz';
end;
begin;
alter table web_stockist alter column contactperson set not null;
alter table web_stockist alter column designation set not null;
end;

how can I get everything into one transaction?
-- 
regards
KG
http://lawgon.livejournal.com