Forum OpenACS Q&A: Technique for adding not null to a column created with alter table

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

I've posted this before, but to actually change the table definition rather than adding a separate constraint, do this:

UPDATE pg_attribute SET attnotnull = TRUE
WHERE attname = 'my_column'
  AND attrelid = (SELECT oid FROM pg_class WHERE relname = 'my_table')

Roger and Jonathan,

You could write a PL/pgSQL function to abstract that and post it to my PostgreSQL Cookbook: http://www.brasileiro.net/postgres/

-Roberto