Re: alter column type from boolean to char with default

Tom Lane wrote:
> Rod Taylor <pg(at)rbt(dot)ca> writes:
> > On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
> >> Hmm ... the way I would have expected to work is
> >>
> >> alter table posts
> >> alter column deleted drop default,
> >> alter column deleted type char(1)
> >> using (case when deleted then 't' else 'f' end),
> >> alter column deleted set default 'f';
>
> > Perhaps it is easiest to allow the user to specify the new default after
> > USING?
>
> He already did --- I don't want to add some random new syntax for this.
>
> Maybe we could hack things so that if both an ALTER TYPE and a SET
> DEFAULT operation are present, we implicitly add a DROP DEFAULT at the
> start. But leave the timing of any explicitly specified DROP DEFAULT
> as-is.

That seems reasonable. I assume it'd throw a notice in that case.

Alternatively, you already have the USING clause to tell you how to
alter the data. How about using it to alter the default as well?
Replace instances of column references with the old default. In this
case, the default would go from
DEFAULT ('f'::boolean)
to
DEFAULT (case when ('f'::boolean) then 't' else 'f' end)

No syntax or grammar change involved, but I'm not sure the additional
semantics adhere to the rule of minimum surprise.

If you wanted to support the DROP CONSTRAINT, ADD CONSTRAINT (which
seems useful) while without confusing it with ADD, DROP (I can't
imagine a use for this), then perhaps tweaking the grammar would be the
answer so that either DROP CONSTRAINT must be the first or ADD
CONSTRAINT must be the last part of an ALTER.

Drew