Thread: Cannot add non-null columns

Cannot add non-null columns

From
teknokrat
Date:
If I go to a tables properties and try adding a non-null column I get an 
error. Adding the from the column view is OK.


Re: Cannot add non-null columns

From
Miha Radej
Date:
hi!

afaik you cannot add a not null column to a table because (in a table 
where there is already some data) the field values are null. the option 
to add a not null column perhaps only works on an empty table with no 
rows yet. so this behaviour is expected.
therefore you need to add a column, update the table to set the column 
values so there is no null fields and then you can alter the column to 
set it a default value, then again to set it no null. this always worked 
for me.

hope it helps.

regards,
Miha


teknokrat wrote:
> If I go to a tables properties and try adding a non-null column I get an 
> error. Adding the from the column view is OK.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 



Re: Cannot add non-null columns

From
teknokrat
Date:
Miha Radej wrote:
> hi!
> 
> afaik you cannot add a not null column to a table because (in a table 
> where there is already some data) the field values are null. the option 
> to add a not null column perhaps only works on an empty table with no 
> rows yet. so this behaviour is expected.
> therefore you need to add a column, update the table to set the column 
> values so there is no null fields and then you can alter the column to 
> set it a default value, then again to set it no null. this always worked 
> for me.
> 

Sorry, I wasn't clear. This is an empty table. After creating it, I 
wanted to add some more columns. I used the properties of the table to 
try adding them but this failed. I then went to the column view and 
added them one at a time and that worked. This behaviour is inconsistent 
and should be fixed. There was no data in the table when I did this.


Re: Cannot add non-null columns

From
Andreas Pflug
Date:
teknokrat wrote:

> 
> Sorry, I wasn't clear. This is an empty table. After creating it, I 
> wanted to add some more columns. I used the properties of the table to 
> try adding them but this failed. I then went to the column view and 
> added them one at a time and that worked. This behaviour is inconsistent 
> and should be fixed. There was no data in the table when I did this.

<hint level="discreet">
My crystal ball tells me you're not using PostgreSQL 8.0....
</hint>

I just checked adding the non-null column from the table properties, 
this works (for an 8.0 server).

Seems we need special 7.x handling.

Regards,
Andreas


Re: Cannot add non-null columns

From
"Virgil Frum"
Date:
Your crystal ball is brilliant. I've tested using PostgreSQL 7.4.2 (SUSE
9.1), and that's the message:

ERROR: adding NOT NULL columns is not implemented
HINT: Add the column, then use ALTER TABLE SET NOT NULL.

Would be great if error messages could be selected and copy/pasted in
message (like Address line of url properties from web brosers).

Regards,
Virgil

----- Original Message -----
From: "Andreas Pflug" <
pgadmin@pse-consulting.de>
To: "teknokrat" <
teknokrat@yahoo.com>
Cc: <
pgadmin-support@postgresql.org>
Sent: Tuesday, October 05, 2004 7:42 PM
Subject: Re: [pgadmin-support] Cannot add non-null columns


>
> <hint level="discreet">
> My crystal ball tells me you're not using PostgreSQL 8.0....
> </hint>
>
> I just checked adding the non-null column from the table properties,
> this works (for an 8.0 server).
>
> Seems we need special 7.x handling.

Re: Cannot add non-null columns

From
teknokrat
Date:
Andreas Pflug wrote:
> teknokrat wrote:
> 
>>
>> Sorry, I wasn't clear. This is an empty table. After creating it, I 
>> wanted to add some more columns. I used the properties of the table to 
>> try adding them but this failed. I then went to the column view and 
>> added them one at a time and that worked. This behaviour is 
>> inconsistent and should be fixed. There was no data in the table when 
>> I did this.
> 
> 
> <hint level="discreet">
> My crystal ball tells me you're not using PostgreSQL 8.0....
> </hint>
> 
> I just checked adding the non-null column from the table properties, 
> this works (for an 8.0 server).
> 
> Seems we need special 7.x handling.

You're absolutely right, I'm on 7.4.1


Re: Cannot add non-null columns

From
Andreas Pflug
Date:
Virgil Frum wrote:

> ERROR: adding NOT NULL columns is not implemented
> HINT: Add the column, then use ALTER TABLE SET NOT NULL.

Fixed in cvs, and contained in the current 2005-10-05 snapshot.

Thanks for reporting!

Regards,
Andreas