Re: alter column type from boolean to char with default
От | Rod Taylor |
---|---|
Тема | Re: alter column type from boolean to char with default |
Дата | |
Msg-id | 1154526983.29712.223.camel@home обсуждение исходный текст |
Ответ на | Re: alter column type from boolean to char with default doesn't work (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: alter column type from boolean to char with default
|
Список | pgsql-sql |
On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote: > "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'; Perhaps it is easiest to allow the user to specify the new default after USING? USING already indicates that a direct conversion may not be what the user wants to do with it? alter table posts alter column deleted type char(1) using (case when deletedthen 't' else 'f' end) default 'f'; Actually, perhaps it would be best to wrap the old default with the USING expression, evaluating when the default was a scalar and keeping the entire equation otherwise? The below SQL would give a new default of 'f' (evaluated scalar): create table posts (deleted bool default false); alter table posts alter column deleted type char(1) using (case when deleted then 't' else 'f' end); This would result in the default (case when somefunc() then 't' else 'f' end): create table posts (deleted bool default somefunc()); alter table posts alter column deleted typechar(1) using (case when deleted then 't' else 'f' end); > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > --
В списке pgsql-sql по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: alter column type from boolean to char with default doesn't work
Следующее
От: Scott MarloweДата:
Сообщение: Re: viewing the description of tables from python DB-API