Re: [Info-ingres] Ingres 3.0.2, alter table... alter column..., strange results

At 2:50 PM +0200 9/7/05, Gerhard Hofmann 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? 

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
.