Subject Re: alter table question
Author adsieben
Hi Eugen,
consider this as the original table:
create table demo(
id integer,
value char default 'y'

You want to change "value" to default 'n':
First you have to add a new temporary field:

alter table demo add temp_value char;

Copy all entries from field "value" to "temp_value":
update demo
set temp_value = value;

Drop the original field:
alter table demo drop value;

Create the new field with new default values:
alter table demo add value char default 'n';

Copy all entries back from temp_value to value:
uptade demo
set value = temp_value
where temp_value != 'y';

Drop the temporary field:
alter table drop temp_value;

I don't know by heart, if the existing fields are filled with the
default value. In that case you have to update the new fields with:
update table demo
set value = 'n'
where value is null;

AFAIK this is also the recommanded procedure when altering a fieldtype.

Best regards / Liebe Grüße aus einem wolkigem Wien

Eugen Stoianovici wrote:
> How do i change the default value of a column?
> Thanks