Forum OpenACS Q&A: impossible to add column not null in postgres?

After loading the acs, I try this:

alter table users add email_me_results_p char(1) default 'f' not null check (email_me_results_p in ('t', 'f'));

But all the rows have nulls in them, and d users shows that there is no "not null" modifier listed.

The only mention of this I could find is a discussion in January to the effect that this can't be done.

I think it goes without saying that this sucks bigtime.

Currently when you add a column, Postgres just marks the new column as  being in existence without expanding the existing tuples in the table  to include it.  Whenever you update a row or insert a new one, the new column is materialized for the new rows.  When old rows are referenced, the non-materialized column is returned as a NULL.

This is very fast, but means that default values and "not null" and the like can't be implemented.  As you've noted, fixing this is on the  todo list, i.e. maintaining the fast existing method for simple "add column" commands, while doing the expensive form if default values or constraints inconsistent with the column being NULL are specified.  Getting rid of the fast, current method isn't a popular idea with thos e who have really huge, multi-GB tables (and PG traditionally has seen  a lot of use as a personal DB for storing huge amounts of astronomical and geographical data).