Обсуждение: BUG #3542: Dropped and recreated columns have problems with NOT NULL contraints

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
pizza_de=# alter table store_flags drop column flag;
ERROR:  column "flag" of relation "store_flags" does not exist
pizza_de=# alter table store_flags add column flag integer not null;
ERROR:  column "flag" contains null values

What did you expect?

Jens Schicke wrote:
> 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

The column was not added. It would've been filled with NULLs, which
would violate the NOT NULL constraint. You have to give a DEFAULT if you
add a column with a NOT NULL constraint.

> pizza_de=# alter table store_flags drop column flag;
> ERROR:  column "flag" of relation "store_flags" does not exist

The column doesn't exist, because the add column above failed, and was
rolled back.

> pizza_de=# alter table store_flags add column flag integer not null;
> ERROR:  column "flag" contains null values

Same as above..

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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.