Skip site navigation (1) Skip section navigation (2)

Re: BUG #3542: Dropped and recreated columns have problems with NOT NULL contraints

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Jens Schicke <j(dot)schicke(at)asco(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3542: Dropped and recreated columns have problems with NOT NULL contraints
Date: 2007-08-16 15:02:16
Message-ID: 20070816075822.T49924@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: 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(dot)schicke(at)asco(dot)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.

In response to

pgsql-bugs by date

Next:From: Scott HarperDate: 2007-08-16 18:48:27
Subject: BUG #3544: SQLException from JDBC driver
Previous:From: Tom LaneDate: 2007-08-16 15:00:21
Subject: Re: BUG #3543: ARRAY(SELECT ...) contruct yields NULL without rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group