I'd like to think I can set a default value to apply only to records inserted from this point on. If I wanted to modify old records which are already in the table I would do an explicit update. I'd be pretty cranky if the dbms changed my null values to some other value.

Paul
ps. MSSQL doesnt support it either.

Paul White wrote:
My assumption was the same: a default value should only affect new rows
but not existing ones.

Regards
Gerhard

At 6:11 PM +0200 9/6/05, Gerhard Hofmann wrote:
While I do have a certain amount of sympathy for this position, I don't
think it's wise to rush into it. The meaning of a relation shouldn't be
time sensitive. To say that some rows have an x column default of V1,
while others have a default for the same column of V2 just because they
happened to be inserted later, rubs me the wrong way. And it still leaves
relatively unanswered the question of just what values does column X
have in pre-existing rows, anyway, if X was added or altered after table
creation? The DBMS is not (currently) in a position to instantiate
anything other than zero-blank-or-null for a column when retrieving a
row that doesn't currently contain that column (because it's an old row).

I wouldn't be fanatically against allowing value defaults for add column
or alter column, but I'd want to see some sort of coherent statement of
semantics that preserves the meaning of pre-existing rows.

Karl

Betty & Karl Schendel wrote:
I am the original poster of this thread, maybe I should have described
my problem in detail.

We have a Java / JDBC based application that should - in a case of
software updates - perform all necessary table structure changes so that
software version and table structures will always match.

In some cases it will be necessary to add a column to an existing table
and this column should have an explicitely defined default value, for
example "Y" or "N".

Unfortunately, Ingres' "alter table add column" statement cannot do
not null default "N"

"alter table add column" can only do
not null with default

My intention was to work around the problem by running "alter table add
column" and then "alter table alter column".

Any way to achieve my goal without using copying out, dropping,
recreating the table?

Regards
Gerhard

At 2:50 PM +0200 9/7/05, Gerhard Hofmann wrote:
Not at present.

I suspect that what we want is an alter table variant that really does
rewrite all the rows when you do alter table add column.
Then you could alter table add column not null [not default initial <value>]
or [with default <value>] and it would initialize the column values.

The hard part is that I'd probably want to implement it within the context
of a modify as well, to avoid logfile overflow problems and row versioning.
That would be nontrivial. (doing it as an alter followed by an update
is not trivial either, but it's simpler. Unfortunately it would be likely
to blow the transaction log for large tables.)

Karl