From: Andrew Hammond <andrew.george.hammond@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: alter column type from boolean to char with default
Date: 3 Aug 2006 11:49:31 -0700
Message-ID: <1154630971.819812.304930@p79g2000cwp.googlegroups.com> (raw)
In-Reply-To: <18063.1154536619@sss.pgh.pa.us>
References: <684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com>
<15041.1154524770@sss.pgh.pa.us>
<1154526983.29712.223.camel@home>
<18063.1154536619@sss.pgh.pa.us>
Tom Lane wrote:
> Rod Taylor <pg@rbt.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew.george.hammond@gmail.com
Subject: Re: alter column type from boolean to char with default
In-Reply-To: <1154630971.819812.304930@p79g2000cwp.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox