I think this is a BUG? - Mailing list pgsql-general

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".

I think that PG should create squence and set it as default, fill the
rows as it does in example2 from the sequence and then make the column
NOT NULL;

r=# begin;
BEGIN
r=# SELECT version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.7 on amd64-portbld-freebsd6.3, compiled by GCC cc (GCC)
3.4.6 [FreeBSD] 20060305
(1 row)

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 |
(1 row)

r=# \d test;
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | text    |
 b      | integer |
 id     | integer | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

regbgrgr=# ALTER TABLE test ADD COLUMN not_null INT NOT NULL ;
ERROR:  column "id" contains null values

==========================================EXAMPLE2======================================
Example2:
In this case the postgress fill the NOT NULL column ID from the sequence.

r=# begin;
BEGIN
r=# SELECT version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.7 on amd64-portbld-freebsd6.3, compiled by GCC cc (GCC)
3.4.6 [FreeBSD] 20060305
(1 row)

r=# CREATE TABLE test( a text, b int);
CREATE TABLE
r=# INSERT INTO test VALUES ('test',1);
INSERT 0 1
regbgrgr=# SELECT * from test;
  a   | b
------+---
 test | 1
(1 row)

r=# CREATE SEQUENCE test_id_seq;
CREATE SEQUENCE
r=# ALTER TABLE test ADD COLUMN id INT NOT NULL PRIMARY KEY default
nextval('test_id_seq'::regclass);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"test_pkey" for table "test"
ALTER TABLE
r=# SELECT * from test;
  a   | b | id
------+---+----
 test | 1 |  1
(1 row)

r=# \d test
                         Table "public.test"
 Column |  Type   |                     Modifiers
--------+---------+---------------------------------------------------
 a      | text    |
 b      | integer |
 id     | integer | not null default nextval('test_id_seq'::regclass)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)


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.
And why creates an NOT NULL column with null values in it!


Best Regards,
  Kaloyan Iliev