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
