Hi All,
I have been having a headache using the Content Repository, and adding attributes with a not null constraint on them.
You will get an error from Postgres 7.2.x stating
ERROR: Adding NOT NULL columns is not implemented.
Add the column, then use ALTER TABLE ADD CONSTRAINT.
Its worth noting that versions from Postgres 7.3 forward support adding not null columns with ALTER TABLE but the above error message seemed silly to me as I did not want to add a TABLE WIDE constraint, I wanted it on a specific column.
Just when I thought I would have to go to Postgres 7.3/OpenACS HEAD, I found this little gem.
how I first tried to do it
create table test (a int);
CREATE
alter table test add column b int not null;
ERROR: Adding NOT NULL columns is not implemented.
Add the column, then use ALTER TABLE ADD CONSTRAINT.
How you CAN acheive it with Pg7.2
create table test (a int);
CREATE
alter table test add column b in;
ALTER
alter table test add constraint bnotnull check (b notnull);
ALTER
viola, its nice that they have added this functionality to 7.3, but there is a workaround for those (like Me) who are using previous versions and have been spoilt by oracle for too long :)
Kudos to Tom Lane who I stole this example from
Roger
