Re: I think this is a BUG?

Kaloyan Iliev wrote:
> Hi,
>
> I find something very interesting which I think is a bug and I want to
> discuss it.
> ---------------------------------------------------------------------------
> Here is the example1:
> 1.I create a table without PK;
> 2.Insert 1 row;
> 3.I ADD PK;
> 4.When I select all ID's are with NULL values, but the column is NOT NULL;
> 5.But If I try to create a regular NOT NULL  column the postgres stops
> me(as it should) with ERROR "ERROR:  column "id" contains null values".

> PostgreSQL 8.2.7 on amd64-portbld-freebsd6.3, compiled by GCC cc (GCC)
> 3.4.6 [FreeBSD] 20060305

> r=# CREATE TABLE test( a text, b int);
> CREATE TABLE
> r=# INSERT INTO test VALUES ('test',1);
> INSERT 0 1
> r=#  ALTER TABLE test ADD COLUMN id INT NOT NULL PRIMARY KEY;
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "test_pkey" for table "test"
> ALTER TABLE
> r=# SELECT * FROM test WHERE id is null;
>  a   | b | id
> ------+---+----
> test | 1 |

Well that's clearly broken (seems to do the same in 8.3 too). I've cc-ed
the hackers list so they can investigate further. Presumably the "not
null" test is being missed somehow when the column is initially created.

> r=# ALTER TABLE test ADD COLUMN not_null int NOT NULL;
> ERROR:  column "not_null" contains null values
>
> My question is why didn't PG create the sequence and fill the values in
> the first example.

Not sure what you mean here.

> And why creates an NOT NULL column with null values in it!

Because it hasn't got any other value to put in it. Try:
ALTER TABLE test ADD COLUMN id3 integer NOT NULL default 0;

--
   Richard Huxton
   Archonet Ltd