Обсуждение: Adding columns NOT NULL

Поиск
Список
Период
Сортировка

Adding columns NOT NULL

От
Laurent ROCHE
Дата:
Hi,

There is a little anomaly in the way adding an extra column is treated in pgAdmin III 1.8.2.
... particularly if this is a NOT NULL column.

When adding the column my_col with a DEFAULT value, the code generated will be:
ALTER TABLE my_table ADD COLUMN my_col boolean;
ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT false;

This works fine ... as long as I don't insert columns with NOT NULL property.
In which case, I SUPPOSE it generates:
ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL;
ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT false;

This will not work, because the column is created with no default (hence no values) and does not support NULL values, hence creation fails.

** In pgAdmin, adding an extra column with NOT NULL property is impossible ! **

So I would suggest to generate the code:
ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL DEFAULT false;

Which does work !
And that allows to create a column with not NULL values, in one operation, and more importantly with the column populated.
At the moment, to perform the same operation I have to
 - create the column, with NULL values.
 - update the table to populate the column
 - modify the column to NOT NULL
... or write my-self the ALTER TABLE statement !


Cheers,
L@u
The Computing Froggy


Envoyé avec Yahoo! Mail.
Une boite mail plus intelligente.

Re: Adding columns NOT NULL

От
Tino Wildenhain
Дата:
Hi,

Laurent ROCHE wrote:
> Hi,
> 
> There is a little anomaly in the way adding an extra column is treated 
> in pgAdmin III 1.8.2.
> ... particularly if this is a NOT NULL column.
> 
> When adding the column my_col with a DEFAULT value, the code generated 
> will be:
> ALTER TABLE my_table ADD COLUMN my_col boolean;
> ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT false;
> 
> This works fine ... as long as I don't insert columns with NOT NULL 
> property.
> In which case, I SUPPOSE it generates:
> ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL;
> ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT false;
> 
> This will not work, because the column is created with no default (hence 
> no values) and does not support NULL values, hence creation fails.
> 
> ** In pgAdmin, adding an extra column with NOT NULL property is 
> impossible ! **
> 
> So I would suggest to generate the code:
> ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL DEFAULT false;
> 
> Which does work !
> And that allows to create a column with not NULL values, in one 
> operation, and more importantly with the column populated.

Yes but it would be too special to just only have "false if boolean".
In this case either the "NOT NULL" option has to go or the
default value should be possible to specify. (otoh, it should
be a rare case anyway that you have a single default value
for all rows)

Regards
Tino

Re: Adding columns NOT NULL

От
"Dave Page"
Дата:
On Fri, Mar 7, 2008 at 10:30 AM, Laurent ROCHE <laurent_roche@yahoo.com> wrote:
>
> So I would suggest to generate the code:
> ALTER TABLE my_table ADD COLUMN my_col boolean NOT NULL DEFAULT false;

It actually does do this already if you add the column through the
table dialogue. I've fixed the column dialogue to do it when called
directly as well (for 1.8.3).

Thanks!

-- 
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk