From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Bertheau <mbertheau.pg@googlemail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: alter column type from boolean to char with default doesn't work
Date: Wed, 02 Aug 2006 09:19:30 -0400
Message-ID: <15041.1154524770@sss.pgh.pa.us> (raw)
In-Reply-To: <684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com>
References: <684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com>
"Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> I basically want to change a boolean column to char. The boolean
> column has a default of true. The char column should have 'f' for
> false and 't' for true. I think that an SQL statement like the
> following should work, but it doesn't:
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';
but that does not work either --- you have to do it in more than one
command:
begin;
alter table posts
alter column deleted drop default;
alter table posts
alter column deleted type char(1)
using (case when deleted then 't' else 'f' end),
alter column deleted set default 'f';
commit;
We could fix this by tweaking ATPrepCmd to schedule drop-default
subcommands in an earlier pass than alter-type, and set-default
subcommands afterwards. However I think the way it's done now
was chosen to avoid surprising behavior in corner cases like
alter table foo
alter column bar set default ...,
alter column bar drop default;
You'd expect this to leave you with no default, but with the change
the DROP part would be re-ordered to occur first. So maybe the
cure is worse than the disease. OTOH that's a pretty silly example,
whereas wanting to ALTER TYPE and fix the default in a single command
is quite reasonable. Thoughts?
regards, tom lane
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: tgl@sss.pgh.pa.us, mbertheau.pg@googlemail.com
Subject: Re: alter column type from boolean to char with default doesn't work
In-Reply-To: <15041.1154524770@sss.pgh.pa.us>
* 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