X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 18C689FB266 for ; Thu, 3 Aug 2006 15:49:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 56764-07 for ; Thu, 3 Aug 2006 15:49:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 3883B9FB24D for ; Thu, 3 Aug 2006 15:49:40 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 98ACC30F5B; Thu, 3 Aug 2006 20:49:38 +0200 (MET DST) From: "Andrew Hammond" X-Newsgroups: pgsql.sql Subject: Re: alter column type from boolean to char with default Date: 3 Aug 2006 11:49:31 -0700 Organization: http://groups.google.com Lines: 42 Message-ID: <1154630971.819812.304930@p79g2000cwp.googlegroups.com> References: <684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com> <15041.1154524770@sss.pgh.pa.us> <1154526983.29712.223.camel@home> <18063.1154536619@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com In-Reply-To: <18063.1154536619@sss.pgh.pa.us> User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.5) Gecko/20060731 Ubuntu/dapper-security Firefox/1.5.0.5,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: p79g2000cwp.googlegroups.com; posting-host=207.219.45.62; posting-account=cSUlcA0AAABWACaPjo2Z9C0GBLTio512 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.102 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200608/29 X-Sequence-Number: 25987 Tom Lane wrote: > Rod Taylor 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