Hi all,

I installed Ingres 3.0.2 on Win XP Pro.

I created a table like this:
create table mytab
(col1 varchar(10) not null default 'WERT',
col2 integer not null default 3);
commit;

Added a new column col3:
alter table mytab add column
col3 varchar(10) not null with default;
commit;

Changed default setting of col3:
alter table mytab alter column
col3 varchar(10) default 'WERT2';
commit;


Ran "help table" to see the result:
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
col1 varchar 10 no value
col2 integer 4 no value
col3 varchar 10 yes null


I would have expected something different for col3 because I had
assigned default value "WERT2".


Tried it slightly different (not nullable + explicit default setting):
alter table mytab alter column
col3 varchar(10) not null default 'WERT2';
commit;


Ran "help table" again:
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
col1 varchar 10 no value
col2 integer 4 no value
col3 varchar 10 no no


Nullability setting seems correct, but where is the default setting for
col3?

TIA
Gerhard

At 3:27 PM +0200 9/6/05, Gerhard Hofmann wrote:
I don't think you can alter the default of a column with ALTER COLUMN.
(What do you do with the column values for all the rows that
existed before you added the column? Those rows all have defaults
for the new column. Ingres doesn't want to go back and rewrite all
the rows with the new default value, and it doesn't know which rows
to rewrite anyway.)

The parser probably should have rejected the statement with an error.

Karl