Re: BUG #3542: Dropped and recreated columns have problems with NOT NULL contraints - Mailing list pgsql-bugs

On Thu, 16 Aug 2007, Jens Schicke wrote:

> The following bug has been logged online:
>
> Bug reference:      3542
> Logged by:          Jens Schicke
> Email address:      j.schicke@asco.de
> PostgreSQL version: 8.2.4
> Operating system:   GNU/Linux
> Description:        Dropped and recreated columns have problems with NOT
> NULL contraints
> Details:
>
> pizza_de=# alter table store_flags add column flag integer;
> ALTER TABLE
> pizza_de=# alter table store_flags drop column flag;
> ALTER TABLE
> pizza_de=# alter table store_flags add column flag integer not null;
> ERROR:  column "flag" contains null values

Yes, because the default is null which means that any existing rows in the
table would violate the constraint as soon as the new column is added with
default values. You need to specify the default at the same time, or if
you don't wish to or cannot do that, make the column, fill it with
non-null values, and then add the not null condition.