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

I think this is a BUG?

From: Kaloyan Iliev <kaloyan(at)digsys(dot)bg>
To: pgsql-general(at)postgresql(dot)org
Subject: I think this is a BUG?
Date: 2008-04-24 08:11:14
Message-ID: 481040A2.7090203@digsys.bg (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Teodor SigaevDate: 2008-04-24 08:11:32
Subject: Re: Index AM change proposals, redux
Previous:From: Bruce MomjianDate: 2008-04-24 04:34:46
Subject: Re: Proposed patch - psql wraps at window width

pgsql-general by date

Next:From: Tomasz OstrowskiDate: 2008-04-24 08:11:54
Subject: Re: Backup setup
Previous:From: Martijn van OosterhoutDate: 2008-04-24 07:31:14
Subject: Re: error connecting to database: could not open relation

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