Обсуждение: Alter column / array

Hi list,

I can't find out why the following command give different results:

database-# CREATE TABLE "test1" (dev_id int, dev_slots integer[] DEFAULT
'{0,0,0,0,0}');
CREATE

If you display (\d test1) the table-structure all looks good - even the
DEFAULTs.

...but if you have an existing table and want to add a column of type array,
it doesn't
work:

database-# CREATE TABLE "test2" (dev_id int);
CREATE
database-# ALTER TABLE "test2" ADD COLUMN dev_slots integer[] DEFAULT
'{0,0,0,0,0}');
ALTER

If you now display the table-structure, the DEFAULT-statement is not listed
and
UPDATE-commands on the ARRAY do strange things.

Any clou?

Regards
R. Lüttecke (IT)

MICHAELTELECOM AG
Bruchheide 34
49163 Bohmte
+49 5471 8060
http://www.michael-telecom.de

On Thu, 11 Oct 2001, [iso-8859-1] Rolf L�ttecke wrote:

> Hi list,
>
> I can't find out why the following command give different results:
>
> database-# CREATE TABLE "test1" (dev_id int, dev_slots integer[] DEFAULT
> '{0,0,0,0,0}');
> CREATE
>
> If you display (\d test1) the table-structure all looks good - even the
> DEFAULTs.
>
> ...but if you have an existing table and want to add a column of type array,
> it doesn't
> work:
>
> database-# CREATE TABLE "test2" (dev_id int);
> CREATE
> database-# ALTER TABLE "test2" ADD COLUMN dev_slots integer[] DEFAULT
> '{0,0,0,0,0}');
> ALTER
>
> If you now display the table-structure, the DEFAULT-statement is not listed
> and
> UPDATE-commands on the ARRAY do strange things.

If I remember correctly, defaults are currently ignored on add
column.  I think you can use alter table alter column to add the default.

And, what do you mean by updates doing strange things?

=?iso-8859-1?Q?Rolf_L=FCttecke?= <rolf.luettecke@michael-telecom.de> writes:
> I can't find out why the following command give different results:

ALTER TABLE ADD COLUMN doesn't support adding a default.  (There is a
test in the code that's supposed to kick out an error message telling
you so, but it doesn't seem to be working.  Need to look at that.)

You can use ALTER TABLE SET DEFAULT to add the default after you add
the column.

Hmm ... I wonder why ADD COLUMN doesn't just call the SET DEFAULT
routine to handle adding the default ...

            regards, tom lane

=?iso-8859-1?Q?Rolf_L=FCttecke?= <rolf.luettecke@michael-telecom.de> writes:
>> I can't find out why the following command give different results:

> ALTER TABLE ADD COLUMN doesn't support adding a default.  (There is a
> test in the code that's supposed to kick out an error message telling
> you so, but it doesn't seem to be working.  Need to look at that.)

I've committed fixes for that problem in 7.2.

> Hmm ... I wonder why ADD COLUMN doesn't just call the SET DEFAULT
> routine to handle adding the default ...

On looking at the archives (see pgsql-hackers around 17-Jul-01),
I see that this wasn't done because it's not an SQL92-compliant
behavior.  Our current implementation of ADD COLUMN can't support
setting the new column's entries to anything except NULL, but the
spec says that if there's a DEFAULT then the column should initially
be filled with that value.

            regards, tom lane

Hi Tom,

> You can use ALTER TABLE SET DEFAULT to add the default after you add
> the column.
Works.

> Hmm ... I wonder why ADD COLUMN doesn't just call the SET DEFAULT
> routine to handle adding the default ...
I'm wondering too. But all programs do only those things they were told
to do - mostly, not always ;-)

Hi Stephan,

> And, what do you mean by updates doing strange things?
Follow-up-errors caused by updating an uninitialized array. Now it is
clear why it couldn't work.

Thanx and regards

R. Lüttecke (IT)

MICHAELTELECOM AG
Bruchheide 34
49163 Bohmte
+49 5471 8060
http://www.michael-telecom.de