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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq